Cost Of Simple count Query

Question

I have a query of simple count on a table. As per below screenshot, there is no primary key on the table except a single index is on different column.

How can I reduce the cost of this query?

Actual query is :

No difference in the cost even after I created an extra index on transactionno column. The table has 45 column. Total size of table is 520MB.

  CREATE TABLE TBL_COUNT 
   (    ROW_NUMBER NUMBER(22,0) NOT NULL ENABLE, 
    DATECREATED TIMESTAMP (6), 
    TRANSACTIONDATE VARCHAR2(64), 
    TRANSACTIONTIME VARCHAR2(64), 
    TRANSACTIONNO VARCHAR2(100), 
    FIRST_NAME VARCHAR2(100), 
    LAST_NAME VARCHAR2(100), 
    REG_NO VARCHAR2(30), 
    EMAIL VARCHAR2(100), 
    PURCHASE_TYPE VARCHAR2(50), 
    RA_STATUS VARCHAR2(50), 
    BSCS_CODE VARCHAR2(20), 
    ORACLE_ITEM_CODE VARCHAR2(100), 
    ORACLE_PACKAGE_CODE VARCHAR2(100), 
    SKU_CODE VARCHAR2(50), 
    ITEM_DESCRIPTIONS VARCHAR2(100), 
    MSISDN VARCHAR2(150), 
    QUANTITY NUMBER(22,0), 
    UNIT_PRICE NUMBER(22,2), 
    SERVICE_TAX NUMBER(22,2), 
    TOTAL_PRICE NUMBER(22,2), 
    PAYMENT_METHOD VARCHAR2(50), 
    PAYMENT_CHANNEL VARCHAR2(50), 
    PAYMENT_MERCHANT_ID VARCHAR2(50), 
    REGISTER_REGION VARCHAR2(30), 
    AR_INTERFACESTATUS VARCHAR2(30), 
    PAYMENT_STATUS VARCHAR2(30), 
    PAYMENT_DATE VARCHAR2(64), 
    PAYMENT_TIME VARCHAR2(64), 
    ISSUING_BANK VARCHAR2(50), 
    CREDIT_CARD_NO VARCHAR2(50), 
    CREDIT_CARD_REASON_CODE VARCHAR2(100), 
    BANK_APPROVAL_CODE VARCHAR2(30), 
    BANK_REGISTER_REGION VARCHAR2(30), 
    BANK_REF_NO VARCHAR2(30), 
    PRIMARY_CONTACT_NO VARCHAR2(30), 
    ALTERNATE_CONTACT_NO VARCHAR2(30), 
    REFERENCE_CONTACT_NO VARCHAR2(30), 
    PRODUCT_UID VARCHAR2(30), 
    BANK_BIN VARCHAR2(50), 
    SETTLEMENT_DATE TIMESTAMP (3), 
    SKU_TYPE VARCHAR2(50), 
    EXTERNAL_ORDER_NUMBER VARCHAR2(64), 
    GST_TAX_AMOUNT NUMBER(22,2), 
    GST_TAX_CODE VARCHAR2(255 )
   )   TABLESPACE TS ;

  CREATE INDEX USER.TBL_COUNT_INDEX ON USER.TBL_COUNT (DATECREATED) 
  TABLESPACE TS_IDX ;

Why are you doing a `count(*)` on a ~1.5 million row table often enough to care about the performance of the query? There is unlikely to be a more efficient approach than doing a table scan unless you want to define a primary key (which you should anyway). You could potentially parallelize the query, that would make it return more quickly but it would also cause it to consume more resources on the server. — Justin Cave, Sep 18 '15 at 06:41
Your edit isn't showing an index on `transactionNo`. Are you certain you created that index? — Justin Cave, Sep 18 '15 at 09:15
I had created the index on transactionno. but as I didn`t found any improvement on the cost, I dropped that — Nitesh Kumar, Sep 18 '15 at 09:43
You most likely want the index and your question probably boils down to asking why the query isn't using the index. Post the DDL you used to create the index. Were stats on the index accurate? How selective is `transactionNo`? Are you binding the correct data type? `transactionNo` seems to be defined as a `varchar2` despite the name implying that it is numeric. — Justin Cave, Sep 18 '15 at 09:50
CREATE INDEX USER.TBL_COUNT_INDEX ON USER.TBL_COUNT (DATECREATED) TABLESPACE TS_IDX ; — Nitesh Kumar, Sep 18 '15 at 10:29
Please edit your question to include that DDL as well as the answers to the other questions. — Justin Cave, Sep 18 '15 at 10:29
HI Justin, Can you please suggest that how can I achieve low cost from the above query.. — Nitesh Kumar, Sep 18 '15 at 10:33
Create the index. Answer the questions I asked. Are the stats on the index accurate? How selective is `transactionNo`? Are you binding the correct data type? — Justin Cave, Sep 18 '15 at 10:34
Stats are updated on daily basis. selection on basis of transactionno is very frequent as for both select and update query. — Nitesh Kumar, Sep 18 '15 at 10:53
How selective is `transationNo`? That is, of the ~1.5 million rows in the table, how many would have any particular `transactionNo`? Are you binding the correct data type? What does `transactionNo` look like? The name implies it is numeric, the definition is a `varchar2(100)`. Is this really a 100 character string? Do all rows share the same leading n characters? Edit your question to include the definition of the index and do a query against `user_indexes` to show us the statistics on that index. — Justin Cave, Sep 18 '15 at 17:17

David Aldridge · Answer 1 · 2015-09-18T12:16:56.553

0

Any index can be used for the count, as long as it is based on a column constrained to be NOT NULL, or if it is otherwise guaranteed to hold a value for every row -- e.g. bitmap index, or an index on (my_column, 0).

Alternatively you can get an estimated number of rows using the SAMPLE clause.

Edit: You say that you created the index on transactionno, and as the DDL shows that is not constrained to be NOT NULL. Either create the index on ROW_NUMBER (the only column that is NOT NULL), or create a function-based index on (transactionno, 0)

edited Sep 18 '15 at 12:16

answered Sep 18 '15 at 06:49

David Aldridge

51,479
8
68
96

The point is when I am looking for explain plan for other table for the same query .. it`s cost is less as it uses primary key. Even after I had created primary key on the same table but no improvement , result in d same cost. – Nitesh Kumar Sep 18 '15 at 07:01
Please show the table and index definitions -- by the way you say in your question that you created an index, not a primary key. – David Aldridge Sep 18 '15 at 07:52
I had created primary key but when I found no improvement in cost, I dropped that. – Nitesh Kumar Sep 18 '15 at 08:36
Add them to the question please – David Aldridge Sep 18 '15 at 08:59
I had created the index on transactionno. but as I didn`t found any improvement on the cost, I dropped that. – Nitesh Kumar Sep 18 '15 at 09:26

Cost Of Simple count Query

1 Answers1