Dear All,
I'm running below query to delete data.
Using Index on Temp Table:
CREATE TEMP TABLE temp2 (cust_numb INTEGER);
CREATE INDEX "informix".temp2_n1 ON temp2(cust_numb) using btree;
INSERT INTO temp2
select first 10000 cb.cust_numb from optim_cs_csdi cb
WHERE EXISTS (SELECT op.cust_numb FROM optim_test_2015_11 op
WHERE op.cust_numb= cb.cust_numb ) ;
DELETE FROM optim_cs_csdi cb WHERE EXISTS (SELECT t1.cust_numb FROM temp2 t1 WHERE cb.cust_numb = t1.cust_numb );
This SQL took total 00.00.44 seconds
type rows_prod est_rows time est_cost-------------------------------------------------nljoin 10000 10000 00:00.44 17958Using No Index on Temp Table:select first 10000 cb.cust_numb from optim_cs_csdi cb
WHERE EXISTS (SELECT op.cust_numb FROM optim_test_2015_11 op
WHERE op.cust_numb= cb.cust_numb ) INTO TEMP temp2;
DELETE FROM optim_cs_csdi cb WHERE EXISTS (SELECT t1.cust_numb FROM temp2 t1 WHERE cb.cust_numb = t1.cust_numb );
This ran in 00:00:38 seconds
type rows_prod est_rows time est_cost-------------------------------------------------nljoin 10000 10000 00:00.38 3038please let me know is it good idea to use index on temp tables?
Will Non Index Temp tables use more CPUs ?
And how to check how many CPU particular SQL using?
Thanks in Advance.
Thanks
Amit Patel
------------------------------
AMIT PATEL
------------------------------
#Informix