Re: Index vs. partitioning for performance

version and edition of the Oracle database server software?

How is data accessed in this table - equijoin, range scan? Hash partitioning for data accessed by a range scan doesn't sound good.

For an 8KB block size, this little test shows that this sample table would be 59 GB.
Partitioning sounds like an excellent idea. 10g R1 standard edition, 10.1.0.4, 10.1.0.4 patchset 10 on w2k3.

  1 create table few_cols_bill_rows (
  2 object_id number,
  3 owner varchar2(30),
  4 object_type varchar2(19),
  5 object_name varchar2(30),
  6 constraint xpk_fcbr primary key (object_id))   7 organization index
  8* tablespace mystuff
me_at_mydb> /

Table created.

me_at_mydb> insert /*+ append */ into few_cols_bill_rows   2 select object_id, owner, object_type, object_name   3 from all_objects
  4 /

64558 rows created.

me_at_mydb> commit;

Commit complete.

me_at_mydb> exec
dbms_stats.gather_table_stats(ownname=>user,tabname=>'FEW_COLS_BILL_ROWS',cascade
=>true,method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');

PL/SQL procedure successfully completed.

  1 select table_name, index_name, num_rows, leaf_blocks, blevel   2 from user_indexes
  3* where table_name='FEW_COLS_BILL_ROWS' me_at_mydb> /

TABLE_NAME INDEX_NAME NUM_ROWS LEAF_BLOCKS BLEVEL

-------------------- --------------- ---------- ----------- ----------
FEW_COLS_BILL_ROWS   XPK_FCBR             64558         502          1

me_at_mydb> -- ok, 64558 rows in 502 leaf_blocks. that's 128 rows per block.
me_at_mydb> -- for 1,000,000,000 rows, that's 7776049 blocks or 59 GB. yeah, partitioning sounds like a very good idea.

-bdbafh Received on Thu Apr 20 2006 - 19:22:07 CDT