Hi Folks,
I am working on tuning of a query based on only one table having > 100 millions of rows.
The table structure is like below
Table customer
cust_id number, - primary key
trx_date date,
firstname varchar2(50),
lastname varchar2(50),
city varchar2(50),
telephone varchar2(50),
code_sign varchar2(2000)
The query looks like
select cust_id,trx_date, code_num
from ( select cust_id,trx_date, code_num
from ( select cust_id,trx_date, function('<var_1>,code_sign) "code_num" -- returns a number
from customer
where ( firstname is not null and firstname ='JOHN' )
OR ( lastname is not null and lastname ='SMITH' )
OR ( city is not null and city ='NEW_YORK' )
OR ( telephone is not null and telephone='123-456-7777' )
union all
select null,null,0 from dual) match_record
order by code_num, trx_date, cust_id DESC ) sorted_records
where rownum = 1;
I want to tune this query to return the result in 1 sec time.
This code is for new application / database and we can consider all the options.
So far I have tried the below options to optimize it (using different Oracle 11g databases)
1. single node DB with SSD storage, 32 cpu and 64 gb ram
2. Oracle 2 node RAC database. (no SSD)
3. single table with four normal indexes (where clause)
4. single table with four four normal indexes including 3 additional columns - trx_date, cust_id,telephone
5. Bitmap indexes based on (4)
6. partition table (12 months) with local indexes based on (4)
7. partition table (12 months) with local indexes based on (5)
8. hint /*+ ALL_ROWS use_concat */
With all these different option I am able to achieve 1 min output time.
I want to further tune it to 1 sec( or close to that )
Will you pls suggest me the options ?
Thanks.
Few update for clarifications
1. JOHN, SMITH ,etc are used as sample names. New values will be passed to the query every time it runs.
2. we have to pass "var_1" to function while running. Var_1 is changing for each run.
3. I also tried creating indexes with "order by DESC" but that did not help.
4. Can not create function based index as the value for Var_1 passed to function is not constant.
5. I tried changing the query by splitting into four parts of where clause and using "UNION ALL" to join the results.
6. The customer table is read-only table, no updated. The table will be complete refresh every week.