Already have function based index created
A reader, November 22, 2016 - 11:53 am UTC
Thanks for your reply Chris .
creating more indexes will create problem in DML..
Could you please share your views..
CREATE INDEX "IDX_CL_TB_UNIT_1" ON "TB_UNIT" ("TEST_KEY", UPPER("UNIT_NAME"), NVL("PURGEID",'-1'))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 83886080 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_DATA" ;
CREATE INDEX "IDX_SUBSTR_PROCESSOR_KEY" ON "TB_UNIT" (SUBSTR("TEST_KEY",24,53))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_DATA" ;
CREATE INDEX "IDX_TB_UNIT_1" ON "TB_UNIT" ("UNIT_ID", "PURGEID", "TEST_KEY")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_DATA" ;
CREATE INDEX "IDX_TB_UNIT_TBR" ON "TB_UNIT" (SUBSTR("TEST_KEY",24), "BANK_NBR", NVL("PURGEID",'-1'))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_DATA" ;
CREATE UNIQUE INDEX "XAK1TB_UNIT" ON "TB_UNIT" ("TEST_KEY")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 66060288 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_DATA" ;
November 22, 2016 - 6:03 pm UTC
Are those the existing indexes? If so none of the match your where clause. So Oracle won't use them! The expression in your index must that in your where clause exactly.
Yes, adding more indexes increases the DML overheads. So it's a balancing act. Which do you need more, fast queries or fast DML?
But as always, measure! See what impact adding an index has on DML and your query. Then make your judgement based on these results.
Suspicious requirement
Andrew Sayer, November 22, 2016 - 8:20 pm UTC
This looks to me very much like you are trying to squeeze as much data into the same column as possible. Why not use different columns to populate the different data?
What does SUBSTR(TEST_KEY,5,8) represent? A lookup to bank data?
Not only will your queries be much easier to write, you can individually index each column (if necessary). The optimizer will behave much nicer for you as it will know for e.g. that one column should only have a two values so an index might not be useful (as you'll get half the table back) or one column has so many different values that an index would be the perfect choice.
You can use foreign keys to enable referential integrity of your data
You won't waste as much storage.
November 23, 2016 - 5:20 am UTC
good input
Virtual column
Ghassan, November 23, 2016 - 4:51 am UTC
Consider adding vc as concat of the 3 predicates.
Moreover if the table is not partitioned do it then by hash on that col.
Consider also indexing that col globally or locally if partitioned.
I prefer the hash partitioning in this case to allow wp. Parallel will be checked if more efficient execution. Anyway tune and tune...
Supplementary
Ghassan, November 23, 2016 - 5:01 am UTC
..and why not using vc as Ora_hash (concat (SUBSTR(TEST_KEY,5,8), SUBSTR(TEST_KEY, 21, 8), SUBSTR(TEST_KEY, 29, 8) ))
Predicate then will be ...
Where My_vc = ora_hash (concat (:1, :2, :3))
..
Parallel Query
Rajeshwaran, Jeyabal, November 23, 2016 - 12:01 pm UTC
....
I have table test_tab having billins of data.
....Since you have a huge data set to deal with, why not just you a Parallel Query in place?
I hope your predicates match up with a huge datasets in table, hence the optimizer has ignore the index in place.
demo@ORAST01> $type d:\script.sql
set serveroutput off
set termout off
select /*+ gather_plan_statistics parallel(big_table,4) */ owner,count(*)
from big_table
group by owner
order by 2 desc;
set termout on
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
demo@ORAST01> @d:\script.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID gy1qtygx34xxm, child number 0
-------------------------------------
select /*+ gather_plan_statistics parallel(big_table,4) */
owner,count(*) from big_table group by owner order by 2 desc
Plan hash value: 2986679575
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 27 |00:00:10.91 | 22 |
| 1 | PX COORDINATOR | | 1 | | 27 |00:00:10.91 | 22 |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 0 | 27 | 0 |00:00:00.01 | 0 |
| 3 | SORT ORDER BY | | 0 | 27 | 0 |00:00:00.01 | 0 |
| 4 | PX RECEIVE | | 0 | 27 | 0 |00:00:00.01 | 0 |
| 5 | PX SEND RANGE | :TQ10001 | 0 | 27 | 0 |00:00:00.01 | 0 |
| 6 | HASH GROUP BY | | 0 | 27 | 0 |00:00:00.01 | 0 |
| 7 | PX RECEIVE | | 0 | 27 | 0 |00:00:00.01 | 0 |
| 8 | PX SEND HASH | :TQ10000 | 0 | 27 | 0 |00:00:00.01 | 0 |
| 9 | HASH GROUP BY | | 0 | 27 | 0 |00:00:00.01 | 0 |
| 10 | PX BLOCK ITERATOR | | 0 | 100M| 0 |00:00:00.01 | 0 |
|* 11 | TABLE ACCESS STORAGE FULL| BIG_TABLE | 0 | 100M| 0 |00:00:00.01 | 0 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- Degree of Parallelism is 4 because of table property
33 rows selected.
demo@ORAST01>