create.mecket.com |
||
.NET/ASP.NET/C#/VB.NET PDF Document SDKThat is, the values SYS and TABLE appear once, and then the third column is stored. In this fashion, we can get many more entries per index block than we could otherwise. This does not decrease concurrency we are still operating at the row level in all cases or functionality at all. It may use slightly more CPU horsepower, as Oracle has to do more work to put together the keys again. On the other hand, it may significantly reduce I/O and allow more data to be cached in the buffer cache, since we get more data per block. That is a pretty good tradeoff. Let s demonstrate the savings by doing a quick test of the preceding CREATE TABLE as SELECT with NOCOMPRESS, COMPRESS 1, and COMPRESS 2. We ll start by creating our IOT without compression: ops$tkyte%ORA11GR2> create table iot 2 ( owner, object_type, object_name, 3 constraint iot_pk primary key(owner,object_type,object_name) 4 ) 5 organization index 6 NOCOMPRESS 7 as 8 select distinct owner, object_type, object_name ssrs code 128 barcode font, ssrs code 39, ssrs fixed data matrix, winforms pdf 417 reader, winforms qr code reader, winforms upc-a reader, itextsharp remove text from pdf c#, find and replace text in pdf using itextsharp c#, winforms ean 13 reader, itextsharp remove text from pdf c#,9 from all_objects 10 / Table created. Now we can measure the space used. We ll use the ANALYZE INDEX VALIDATE STRUCTURE command for this. This command populates a dynamic performance view named INDEX_STATS, which will contain only one row at most with the information from the last execution of that ANALYZE command: ops$tkyte%ORA11GR2> analyze index iot_pk validate structure; Index analyzed. ops$tkyte%ORA11GR2> select lf_blks, br_blks, used_space, 2 opt_cmpr_count, opt_cmpr_pctsave 3 from index_stats; LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE ---------- ---------- ---------- -------------- ---------------429 3 3081451 2 33 This shows our index is currently using 429 leaf blocks (where our data is) and 3 branch blocks (blocks Oracle uses to navigate the index structure) to find the leaf blocks. The space used is about 3MB (3,081,451 bytes). The other two oddly named columns are trying to tell us something. The OPT_CMPR_COUNT (optimum compression count) column is trying to say, If you made this index COMPRESS 2, you would achieve the best compression. The OPT_CMPR_PCTSAVE (optimum compression percentage saved) is telling us if we did the COMPRESS 2, we would save about one-third of the storage and the index would consume just two-thirds the disk space it is now. COM components can be easily consumed from F# programs, and the opposite is also possible by exposing .NET objects as COM components. The following example is similar to the one discussed in the COM Metadata and Windows Registry sidebar; it is based on the Windows Scripting Host but uses F# and fsi.exe: To test that theory, we ll rebuild the IOT with COMPRESS 1 first: ops$tkyte%ORA11GR2> alter table iot move compress 1; Table altered. ops$tkyte%ORA11GR2> analyze index iot_pk validate structure; Index analyzed. ops$tkyte%ORA11GR2> select lf_blks, br_blks, used_space, 2 opt_cmpr_count, opt_cmpr_pctsave 3 from index_stats; LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE ---------- ---------- ---------- -------------- ---------------372 3 2666971 2 22 As you can see, the index is in fact smaller: about 2.6MB, with far fewer leaf. But now it is saying, You can still get another 22% off, as we didn t chop off that much yet. Let s rebuild with COMPRESS 2: ops$tkyte%ORA11GR2> alter table iot move compress 2; Table altered. ops$tkyte%ORA11GR2> analyze index iot_pk validate structure; Index analyzed. ops$tkyte%ORA11GR2> select lf_blks, br_blks, used_space, 2 opt_cmpr_count, opt_cmpr_pctsave 3 from index_stats; LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE ---------- ---------- ---------- -------------- ---------------286 3 2052864 2 0 Now we are significantly reduced in size, both by the number of leaf blocks as well as overall used space, by about 2MB. If we go back to the original numbers ops$tkyte%ORA11GR2> select (2/3)*3081451 from dual; (2/3)*3081451 ------------2054300.67 we can see the OPT_CMPR_PCTSAVE was pretty much dead-on accurate. The preceding example points out an interesting fact with IOTs. They are tables, but only in name. Their segment is truly an index segment. I am going to defer discussion of the PCTTHRESHOLD option at this point, as it is related to the next two options for IOTs: OVERFLOW and INCLUDING. If we look at the full SQL for the next two sets of tables, T2 and T3, we see the following (I ve used a DBMS_METADATA routine to suppress the storage clauses, as they are not relevant to the example): ops$tkyte%ORA11GR2> begin 2 dbms_metadata.set_transform_param 3 ( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false ); 4 end; 5 / PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> select dbms_metadata.get_ddl( 'TABLE', 'T2' ) from dual; DBMS_METADATA.GET_DDL('TABLE','T2') ------------------------------------------------------------------------------CREATE TABLE "OPS$TKYTE"."T2" ( "X" NUMBER(*,0), "Y" VARCHAR2(25), "Z" DATE, PRIMARY KEY ("X") ENABLE ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING TABLESPACE "USERS"
|