I am trying to write a query that only extract the first (random) row when condition is met.
-- Create table
create table TRANSACTIONS_SAMPLE
(
institution_id NUMBER(5) not null,
id NUMBER(10) not null,
partitionkey NUMBER(10) default 0 not null,
cardid NUMBER(10),
accountid NUMBER(10),
batchid NUMBER(10) not null,
amt_bill NUMBER(16,3),
load_date DATE not null,
trxn_date DATE not null,
single_msg_flag NUMBER(5),
authaccounttype VARCHAR2(2 BYTE),
originator VARCHAR2(50),
amount NUMBER(16,3) default 0.000 not null,
embeddedfee NUMBER(16,3) default 0.000 not null,,
valuedate DATE,
startofinterest DATE,
minduevaluedate DATE,
postdate DATE,
posttimestamp DATE,
Status CHAR(4 BYTE) default 'NEW' not null,
)
partition by list (PARTITIONKEY)
(
partition 0002913151 values (1234567)
tablespace LIVE
pctfree 10
initrans 16
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
)
);
-- Create/Recreate indexes
create index TRANSACTIONS_SAMPLEI01 on TRANSACTIONS_SAMPLE (ACCOUNTID)
local;
create index TRANSACTIONS_SAMPLEI02 on TRANSACTIONS_SAMPLE (LOAD_DATE)
local;
create index TRANSACTIONS_SAMPLEI03 on TRANSACTIONS_SAMPLE (BATCHID)
local;
create index TRANSACTIONS_SAMPLEI04 on TRANSACTIONS_SAMPLE (POSTDATE)
local;
create index TRANSACTIONS_SAMPLEI05 on TRANSACTIONS_SAMPLE (POSTTIMESTAMP)
local;
create index TRANSACTIONS_SAMPLEI06 on TRANSACTIONS_SAMPLE (STATUS, PARTITIONKEY)
local;
create index TRANSACTIONS_SAMPLEI07 on TRANSACTIONS_SAMPLE (CARDID, TRXN_DATE)
local;
create unique index TRANSACTIONS_SAMPLEUI01 on TRANSACTIONS_SAMPLE (ID, PARTITIONKEY)
local;
-- Create/Recreate primary, unique and foreign key constraints
alter table TRANSACTIONS_SAMPLE
add constraint TRANSACTIONS_SAMPLEPK primary key (ID, PARTITIONKEY);
--QUERY
Select * From (
Select t.AccountId From Transactions_sample t Group by t.Accountid Having Count(t.AccountId) > 10 order by dbms_random.random)
Where Rownum = 1
The problem With this Query is full table scan. I want to achieve the same results without having to fully Access the table. Any ideas?
Thanks
You can get it down to a full index scan, using TRANSACTIONS_SAMPLEI01, if you add a filter for where AccountId is not null. But only if you don't want to count null values, of course.
The column is nullable, but the index doesn't contain null values. To include the count of nulls it has to do a full table scan because it cannot get that count from the index. If you have that filter the optimizer knows all the account ID values must be in the index, so it only has to refer to that, not the table itself.
explain plan for
Select * From (
Select t.AccountId From Transactions_sample t where AccountId is not null Group by t.Accountid Having Count(t.AccountId) > 10 order by dbms_random.random)
Where Rownum = 1;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 381125580
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 1 | 13 | 0 (0)| 00:00:01 | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 13 | 0 (0)| 00:00:01 | | |
|* 4 | FILTER | | | | | | | |
| 5 | SORT GROUP BY NOSORT | | 1 | 13 | 0 (0)| 00:00:01 | | |
| 6 | PARTITION LIST SINGLE| | 1 | 13 | 0 (0)| 00:00:01 | 1 | 1 |
|* 7 | INDEX FULL SCAN | TRANSACTIONS_SAMPLEI01 | 1 | 13 | 0 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
4 - filter(COUNT("T"."ACCOUNTID")>10)
7 - filter("ACCOUNTID" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Alternatively, if the column can be made not-nullable then the filter wouldn't be required.