-
slow in inserting to Temp table.
hi
I have the following query, whcih is very fast enough in select the data but the same qry if i put to isert into a temp table it is taking lot of time.the total no of records is 87000 from select statement.
pl help me in this query.
index are there on purchase_log table.
INSERT INTO temp_transd1(bt_option,bt_trno,bt_seqno,bt_processdt,bt_units,bt_agent,bt_purred,bt_acno)
SELECT pl.fund_option,pl.txn_no,SEQ_NO,pl.batch_run_date,pl.units_allotted,pl.distributor_id,
pl.txn_source,pl.account_number
FROM purchase_log pl
WHERE pl.distributor_id =Trim(o_brokcode)
AND pl.auth_date <=i_trdate
AND pl.txn_status <> 'R' ;
thnks
sk_naidu
sknaidu
-
check the table storage clauses.
what is your initial, next extent size, freelists ,parallelism and initrans set to
Suvashish
-
hi suvashish
the table is a global tempporary table and thescript s below.
CREATE GLOBAL TEMPORARY TABLE TEMP_TRANSD1
(
BT_OPTION VARCHAR2(6),
BT_BRANCH VARCHAR2(6),
BT_TRNO VARCHAR2(15),
BT_PROCESSDT DATE,
BT_UNITS NUMBER(20,3),
BT_AGENT VARCHAR2(16),
BT_PURRED VARCHAR2(8),
BT_ACNO VARCHAR2(16),
BT_SEQNO NUMBER(4)
)
ON COMMIT PRESERVE ROWS;
thnks
sknaidu
-
hi suvashish
here are the details.
PCT_FREE 10
PCT_USED 40
INI_TRANS 1
MAX_TRANS 255
FREELIST1
FREELIST_GROUP 1
LOGGING NO
BACKED_UP N
thanks
sknaidu
-
use APPEND hint with nologging
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
abhaysk
IT DIDN'T WORK.
THANKS
sknaidu
-
try increasing the freelists and initrans to the number of CPU.
Suvashish
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|