Use Exists instead of IN

dont use : 27 seconds

select count(*) from pro 
where pro_id in
( select  pro_id from sales)

OK : 20 seconds

select count(*) from pro p
where exists
      ( select 1 from sales s
          where s.pro_id=c.pro_id

*

index should be created in different tablespace with table

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=h:\oradata\archive' SCOPE=BOTH;

ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST

Solution :

1. Reset the LOG_ARCHIVE_DEST to NULL value :

   SQL> ALTER SYSTEM set log_archive_dest='';

2. Set the LOG_ARCHIVE_DEST_1 to the value set to LOG_ARCHIVE_DEST before
  
   SQL> ALTER SYSTEM set log_archive_dest_1='LOCATION=/...';

if you use this code, you dont need to restart database:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=h:\oradata\archive' SCOPE=BOTH; 

To check archival destination issue is ok:

show parameter LOG_ARCHIVE_DEST; 
select DEST_ID, DEST_NAME,DESTINATION from v$archive_dest;