title: Oracle partitioned table query cost vs non-partitioned table query cost
body: <p>I have a table <strong>PO_HEADER</strong> with ~20 million records. Considering our future load on the table we have decided to partitioned the table to increase the performance of the sql queries. Below are the queries used to create the new partitioned tables.</p>
  
  <pre><code>CREATE TABLE PO_HEADER_LP 
   PARTITION BY LIST (BUYER_IDENTIFIER)
  (PARTITION GC66287246AA VALUES ('GC66287246AA') TABLESPACE MITRIX_TABLES,
  PARTITION GC43837235JK VALUES ('GC43837235JK') TABLESPACE MITRIX_TABLES,
  PARTITION GC84338293AA VALUES ('GC84338293AA') TABLESPACE MITRIX_TABLES,
  PARTITION DEFAULTBUID VALUES (DEFAULT) TABLESPACE MITRIX_TABLES) 
  AS SELECT * 
     FROM PO_HEADER;
  
  create index PO_HEADER_LP_SI_IDX on PO_HEADER_LP("SUPPLIER_IDENTIFIER") TABLESPACE MITRIX_INDEXES LOCAL;
  </code></pre>
  
  <p>Old Table PO_HEADER has two indexes on "BUYER_IDENTIFIER" and "SUPPLIER_IDENTIFIER" columns as follows:</p>
  
  <pre><code>create index PO_HEADER_BI_IDX on  PO_HEADER("BUYER_IDENTIFIER") TABLESPACE MITRIX_INDEXES;
  create index PO_HEADER_SI_IDX on  PO_HEADER("SUPPLIER_IDENTIFIER") TABLESPACE MITRIX_INDEXES;
  </code></pre>
  
  <p>To test the performance of the query, I executed below query on both the tables. But, to my wonder I saw the cost of the 2nd query is almost double than the 1st one. Can any body know, why is the query cost is high of the partitioned table compared to normal table. Thanks in Advance.</p>
  
  <pre><code>select * from po_header where buyer_identifier='GC84338293AA' and supplier_identifier='GC75987723HT'; --cost: 56,941
  select * from po_header_lp where buyer_identifier= 'GC84338293AA' and supplier_identifier='GC75987723HT'; --cost: 93,309
  </code></pre>
  
  <p>PO_HEADER with Global Index on buyer_identifier &amp; supplier_identifier column
  <a href="https://i.stack.imgur.com/64AXX.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/64AXX.png" alt="PO_HEADER with Global Index on buyer_identifier &amp; supplier_identifier column"></a></p>
  
  <p>PO_HEADER_LP with Global Index on supplier_identifier column
  <a href="https://i.stack.imgur.com/iJTBY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iJTBY.png" alt="PO_HEADER_LP with Global Index on supplier_identifier column"></a></p>
  
  <p>PO_HEADER_LP with Local Index on supplier_identifier column
  <a href="https://i.stack.imgur.com/zISu8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zISu8.png" alt="PO_HEADER_LP with Local Index on supplier_identifier column"></a></p>