Home » SQL & PL/SQL » SQL & PL/SQL » Performance issue is sql query (3 Merged)
Performance issue is sql query (3 Merged) [message #567065] Mon, 24 September 2012 00:01 Go to next message
kkumar88
Messages: 13
Registered: September 2012
Location: INDIA
Junior Member
Dear all,

The below mentioned query taking too much time generate the output.

The mentioned table having 5844446 records.(table name Id_equipment_all_moves).

Note : The table doesn't having parition and this table used to store information about container movements.

The iv_equipment_all_moves_gv view and Id_equipment_all_moves table both are having same data only different

Id_equipment_all_moves table working based on filter iv_equipment_all_moves_gv view working based on the without filter.

Example : India location having 10filters
container1 having LIKE MOVES GATEOUT,DSFULL,DVAN,CSTUFF,IOR,EXPIN,VAN,LDFULL,GATEIN.


select /*+ INDEX(ID_EQUIPMENT_ALL_MOVES EQPA_MOVE_DATE_IDX)*/ 'Full Import on Terminal' move_code, eqpa_container_type, count(eqpa_container) cont,
sum(nvl(decode(eqpa_container_size, '20', 1, '40', 2),0)) teus
from id_equipment_all_moves
where eqpa_company = 'MTU'
and eqpa_move_Date between '01-JAN-2009' AND '23-SEP-2012'
and eqpa_move_code ='DSFULL'
and (eqpa_company,eqpa_container,eqpa_serial_no)
in (select a.eqpa_company,a.eqpa_container,max(a.eqpa_serial_no)
from iv_equipment_all_moves_gv a, id_equipment_control
where a.eqpa_company = 'MTU'
and a.eqpa_company = eqip_company
and a.eqpa_container = eqpa_container
and a.eqpa_container_type = eqpa_container_type
and a.eqpa_move_date <= SYSDATE
and eqpa_soc = 'L'
group by a.eqpa_company,a.eqpa_container )
and (eqpa_current_loc_depot='TRIST')
group by eqpa_container_type

Kindly check and give me postive solution for this issue.

  • Attachment: tur.JPG
    (Size: 79.04KB, Downloaded 86 times)
Performance issue is sql query [message #567067 is a reply to message #567065] Mon, 24 September 2012 00:05 Go to previous messageGo to next message
kkumar88
Messages: 13
Registered: September 2012
Location: INDIA
Junior Member
Dear all,

The below mentioned query taking too much time generate the output.

The mentioned table having 5844446 records.(table name Id_equipment_all_moves).

Note : The table doesn't having parition and this table used to store information about container movements.

The iv_equipment_all_moves_gv view and Id_equipment_all_moves table both are having same data only different

Id_equipment_all_moves table working based on filter iv_equipment_all_moves_gv view working based on the without filter.

Example : India location having 10filters
container1 having LIKE MOVES GATEOUT,DSFULL,DVAN,CSTUFF,IOR,EXPIN,VAN,LDFULL,GATEIN.


select /*+ INDEX(ID_EQUIPMENT_ALL_MOVES EQPA_MOVE_DATE_IDX)*/ 'Full Import on Terminal' move_code, eqpa_container_type, count(eqpa_container) cont,
sum(nvl(decode(eqpa_container_size, '20', 1, '40', 2),0)) teus
from id_equipment_all_moves
where eqpa_company = 'MTU'
and eqpa_move_Date between '01-JAN-2009' AND '23-SEP-2012'
and eqpa_move_code ='DSFULL'
and (eqpa_company,eqpa_container,eqpa_serial_no)
in (select a.eqpa_company,a.eqpa_container,max(a.eqpa_serial_no)
from iv_equipment_all_moves_gv a, id_equipment_control
where a.eqpa_company = 'MTU'
and a.eqpa_company = eqip_company
and a.eqpa_container = eqpa_container
and a.eqpa_container_type = eqpa_container_type
and a.eqpa_move_date <= SYSDATE
and eqpa_soc = 'L'
group by a.eqpa_company,a.eqpa_container )
and (eqpa_current_loc_depot='TRIST')
group by eqpa_container_type


Re: Performance issue is sql query (3 Merged) [message #567070 is a reply to message #567065] Mon, 24 September 2012 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59504
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Performance issue is sql query (3 Merged) [message #567113 is a reply to message #567070] Mon, 24 September 2012 04:45 Go to previous messageGo to next message
kkumar88
Messages: 13
Registered: September 2012
Location: INDIA
Junior Member
Dear all,

The below mentioned query taking too much time generate the output.

SELECT 'Full Import on Terminal'                         move_code, 
       eqpa_container_type, 
       Count(eqpa_container)                             cont, 
       SUM(Nvl(Decode(eqpa_container_size, '20', 1, 
                                           '40', 2), 0)) teus 
FROM   id_equipment_all_moves 
WHERE  eqpa_company = 'MTU' 
       AND eqpa_move_date BETWEEN '01-JAN-2009' AND '23-SEP-2012' 
       AND eqpa_move_code = 'DSFULL' 
       AND ( eqpa_company, eqpa_container, eqpa_serial_no ) IN 
           (SELECT a.eqpa_company, 
       a.eqpa_container, 
       Max(a.eqpa_serial_no) 
       FROM   iv_equipment_all_moves_gv a, 
       id_equipment_control 
       WHERE  a.eqpa_company = 'MTU' 
       AND a.eqpa_company = eqip_company 
       AND a.eqpa_container = eqpa_container 
       AND a.eqpa_container_type = eqpa_container_type 
       AND a.eqpa_move_date <= SYSDATE 
       AND eqpa_soc = 'L' 
       GROUP  BY a.eqpa_company, 
       a.eqpa_container) 
GROUP  BY eqpa_container_type

Also please note database version is oracle Database 10g Enterprise Edition, Release 10.2.0.5.0 - 64bit,Redhat Enterprice LInux-5.2

*BlackSwan corrected {code} tags

[Updated on: Mon, 24 September 2012 09:33] by Moderator

Report message to a moderator

Re: Performance issue is sql query (3 Merged) [message #567114 is a reply to message #567113] Mon, 24 September 2012 04:46 Go to previous messageGo to next message
kkumar88
Messages: 13
Registered: September 2012
Location: INDIA
Junior Member
Dear Michel,

Please check and give me best solution.

Regards,
Kumar.P
Re: Performance issue is sql query (3 Merged) [message #567118 is a reply to message #567114] Mon, 24 September 2012 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59504
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As soon as you comply the rules.

Regards
Michel
Re: Performance issue is sql query (3 Merged) [message #567122 is a reply to message #567118] Mon, 24 September 2012 06:15 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Instead of IN clause try with exists clause
Re: Performance issue is sql query (3 Merged) [message #567123 is a reply to message #567122] Mon, 24 September 2012 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59504
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
./fa/449/0/ Hopefully you are still there to make my day.

Regards
Michel
Re: Performance issue is sql query (3 Merged) [message #567142 is a reply to message #567123] Mon, 24 September 2012 09:43 Go to previous messageGo to next message
kkumar88
Messages: 13
Registered: September 2012
Location: INDIA
Junior Member
Dear Michal,

Sorry for the delay.

Kindly refer attaced trace and table script for your reference.
  • Attachment: trace.txt
    (Size: 57.62KB, Downloaded 78 times)
Re: Performance issue is sql query (3 Merged) [message #567143 is a reply to message #567142] Mon, 24 September 2012 09:46 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
Senior Member

ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Performance issue is sql query (3 Merged) [message #567144 is a reply to message #567143] Mon, 24 September 2012 10:06 Go to previous messageGo to next message
kkumar88
Messages: 13
Registered: September 2012
Location: INDIA
Junior Member
Dear All,

Kindly refer the attached file and give the best solution.
  • Attachment: trace.txt
    (Size: 61.47KB, Downloaded 172 times)
Re: Performance issue is sql query (3 Merged) [message #567166 is a reply to message #567144] Mon, 24 September 2012 14:41 Go to previous messageGo to next message
Flyby
Messages: 146
Registered: March 2011
Location: Belgium
Senior Member
Kkum88, can you provide the ddl as Blackswan asked? As some sample data (insert statements) for a testcase? I'm scared of the use of "eqpa_move_date BETWEEN '01-JAN-2009' AND '23-SEP-2012'" without the to_date function in case datenotation changes. iv_equipment_all_moves_gv is also a black box.
Re: Performance issue is sql query (3 Merged) [message #567188 is a reply to message #567166] Tue, 25 September 2012 01:44 Go to previous messageGo to next message
kkumar88
Messages: 13
Registered: September 2012
Location: INDIA
Junior Member
Dear All,

The attached query having 2 tables and one view.

ID_EQUIPMENT_ALL_MOVES --- 5844446 records

ID_EQUIPMENT_CONTROL --- 1 record

iv_equipment_all_moves_gv --- view based ID_EQUIPMENT_ALL_MOVES table.

The differnece between ID_EQUIPMENT_ALL_MOVES table and iv_equipment_all_moves_gv ,ID_EQUIPMENT_ALL_MOVES working based on the filter and iv_equipment_all_moves_gv will not consider the filter.

Kindly refer the attched DDL script for your reference.

Regards,

Kumar.P
  • Attachment: T_1234.txt
    (Size: 18.78KB, Downloaded 87 times)
Re: Performance issue is sql query (3 Merged) [message #567192 is a reply to message #567188] Tue, 25 September 2012 02:08 Go to previous messageGo to next message
kkumar88
Messages: 13
Registered: September 2012
Location: INDIA
Junior Member
Dear sirs,

Am Waiting your reply....
Re: Performance issue is sql query (3 Merged) [message #567217 is a reply to message #567192] Tue, 25 September 2012 06:50 Go to previous messageGo to next message
kkumar88
Messages: 13
Registered: September 2012
Location: INDIA
Junior Member
Dear sir,

what happened... Pls check and give me positive solution
Re: Performance issue is sql query (3 Merged) [message #567218 is a reply to message #567217] Tue, 25 September 2012 07:01 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Can you create index on these columns ..and try let me know

eqpa_company, eqpa_container, eqpa_serial_no 
Re: Performance issue is sql query (3 Merged) [message #567220 is a reply to message #567218] Tue, 25 September 2012 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59504
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why?

Regards
Michel
Re: Performance issue is sql query (3 Merged) [message #567226 is a reply to message #567220] Tue, 25 September 2012 08:04 Go to previous messageGo to next message
kkumar88
Messages: 13
Registered: September 2012
Location: INDIA
Junior Member
Dear Michel,

We have already unique index on the columns eqpa_company, eqpa_container, eqpa_serial_no .

Please give the solution
Re: Performance issue is sql query (3 Merged) [message #567228 is a reply to message #567226] Tue, 25 September 2012 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59504
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't download files.

Regards
Michel
Re: Performance issue is sql query (3 Merged) [message #567230 is a reply to message #567228] Tue, 25 September 2012 08:15 Go to previous messageGo to next message
kkumar88
Messages: 13
Registered: September 2012
Location: INDIA
Junior Member

Dear Michel,

Kindly advice which files are you uable to download
Re: Performance issue is sql query (3 Merged) [message #567231 is a reply to message #567230] Tue, 25 September 2012 08:15 Go to previous messageGo to next message
kkumar88
Messages: 13
Registered: September 2012
Location: INDIA
Junior Member

Dear Michel,

Iam very new from this site... Please help to solve the my slowness issue
Re: Performance issue is sql query (3 Merged) [message #567260 is a reply to message #567230] Tue, 25 September 2012 12:38 Go to previous messageGo to next message
joy_division
Messages: 4532
Registered: February 2005
Location: East Coast USA
Senior Member
kkumar88 wrote on Tue, 25 September 2012 09:15

Kindly advice which files are you uable to download


Many people cannot or will not download file attachments. Posting them inline within the message body is what most people will do.
Re: Performance issue is sql query (3 Merged) [message #567278 is a reply to message #567260] Wed, 26 September 2012 01:02 Go to previous messageGo to next message
kkumar88
Messages: 13
Registered: September 2012
Location: INDIA
Junior Member
Now what can i do for this please advice
Re: Performance issue is sql query (3 Merged) [message #567333 is a reply to message #567278] Wed, 26 September 2012 14:02 Go to previous message
Flyby
Messages: 146
Registered: March 2011
Location: Belgium
Senior Member
Almost there kkumar88. Just some sample data needed.
I would check if the joins in your where clause are properly indexed and fix the date clause
with proper to_dates as
BETWEEN '01-JAN-2009' AND '23-SEP-2012' -> BETWEEN TO_DATE('01-JAN-2009','DD-MON-YYYY') and TO_DATE('23-SEP-2012','DD-MON-YYYY')
As we don't download files: fileinfo in code tag.
As the trace, please remove the "sys" operations (an option in tkprof)
SELECT 'Full Import on Terminal'                         move_code, 
       eqpa_container_type, 
       Count(eqpa_container)                             cont, 
       SUM(Nvl(Decode(eqpa_container_size, '20', 1, 
                                           '40', 2), 0)) teus 
FROM   mtu_2009.id_equipment_all_moves 
WHERE  eqpa_company = 'MTU' 
       AND eqpa_move_date BETWEEN '01-JAN-2009' AND '23-SEP-2012' 
       AND eqpa_move_code = 'DSFULL' 
       AND ( eqpa_company, eqpa_container, eqpa_serial_no ) IN 
           (SELECT a.eqpa_company, 
       a.eqpa_container, 
       Max(a.eqpa_serial_no) 
       FROM   mtu_2009.iv_equipment_all_moves_gv a, 
       mtu_2009.id_equipment_control 
       WHERE  a.eqpa_company = 'MTU' 
       AND a.eqpa_company = eqip_company 
       AND a.eqpa_container = eqpa_container 
       AND a.eqpa_container_type = eqpa_container_type 
       AND a.eqpa_move_date <= SYSDATE 
       AND eqpa_soc = 'L' 
       GROUP  BY a.eqpa_company, 
       a.eqpa_container) 
GROUP  BY eqpa_container_type


CREATE TABLE id_equipment_all_moves ( eqpa_company VARCHAR2(10) NOT NULL, eqpa_container VARCHAR2(15) NOT NULL, eqpa_serial_no NUMBER NOT NULL, eqpa_move_date DATE NOT NULL, eqpa_move_code VARCHAR2(10) NOT NULL, eqpa_service_type VARCHAR2(10) NOT NULL, eqpa_container_service_type VARCHAR2(10), eqpa_container_type VARCHAR2(10) NOT NULL, eqpa_container_size VARCHAR2(2) NOT NULL, eqpa_container_storage VARCHAR2(10) NOT NULL, eqpa_mode VARCHAR2(1) DEFAULT 'S' NOT NULL, eqpa_current_location VARCHAR2(5) NOT NULL, eqpa_current_status VARCHAR2(1) DEFAULT 'C' NOT NULL, eqpa_gross_weight NUMBER DEFAULT 0 NOT NULL, eqpa_tare_weight NUMBER DEFAULT 0 NOT NULL, eqpa_net_weight NUMBER DEFAULT 0 NOT NULL, eqpa_cargo_weight NUMBER DEFAULT 0 NOT NULL, eqpa_days NUMBER DEFAULT 0 NOT NULL, eqpa_created_by VARCHAR2(10) NOT NULL, eqpa_created_on DATE DEFAULT sysdate NOT NULL, eqpa_modified_by VARCHAR2(10), eqpa_modified_on DATE, eqpa_line VARCHAR2(10), eqpa_vessel VARCHAR2(10), eqpa_voyage VARCHAR2(10), eqpa_port VARCHAR2(5), eqpa_mother_line VARCHAR2(10), eqpa_mother_vessel VARCHAR2(10), eqpa_mother_voyage VARCHAR2(10), eqpa_mother_port VARCHAR2(5), eqpa_origin_port VARCHAR2(5), eqpa_origin_name VARCHAR2(50), eqpa_from_port VARCHAR2(5), eqpa_to_port VARCHAR2(5), eqpa_final_port VARCHAR2(5), eqpa_final_name VARCHAR2(50), eqpa_truck_number VARCHAR2(25), eqpa_transporter VARCHAR2(50), eqpa_customer VARCHAR2(10), eqpa_seal_number VARCHAR2(25), eqpa_bill_no VARCHAR2(50), eqpa_bl_number VARCHAR2(25), eqpa_do_number VARCHAR2(25), eqpa_remarks VARCHAR2(240), eqpa_out_gauge VARCHAR2(1) DEFAULT 'N', eqpa_out_height NUMBER, eqpa_out_width NUMBER, eqpa_out_length NUMBER, eqpa_reefer_status VARCHAR2(1) DEFAULT 'N', eqpa_min_temprature NUMBER, eqpa_max_temprature NUMBER, eqpa_imco_status VARCHAR2(1) DEFAULT 'N', eqpa_imco_unno VARCHAR2(10), eqpa_imco_class VARCHAR2(10), eqpa_imco_page VARCHAR2(10), eqpa_imco_flash VARCHAR2(10), eqpa_food_status VARCHAR2(1) DEFAULT 'N', eqpa_food_description VARCHAR2(240), eqpa_booking_type VARCHAR2(10), eqpa_booking_number VARCHAR2(25), eqpa_job_type VARCHAR2(10), eqpa_job_number VARCHAR2(25), eqpa_job_basis VARCHAR2(50), eqpa_amount NUMBER DEFAULT 0, eqpa_train_number VARCHAR2(25), eqpa_train_reference VARCHAR2(50), eqpa_account_status VARCHAR2(1) DEFAULT 'X', eqpa_booking_status VARCHAR2(1) DEFAULT 'X', eqpa_billing_status VARCHAR2(1) DEFAULT 'X', eqpa_flex_1 VARCHAR2(240), eqpa_flex_2 VARCHAR2(240), eqpa_flex_3 VARCHAR2(240), eqpa_flex_4 VARCHAR2(240), eqpa_flex_5 VARCHAR2(240) DEFAULT 'N', eqpa_entry_type VARCHAR2(1) DEFAULT 'D' NOT NULL, eqpa_stowage VARCHAR2(10), eqpa_commodity VARCHAR2(10), eqpa_sequence NUMBER, eqpa_soc VARCHAR2(1) DEFAULT 'L', eqpa_voyage_service VARCHAR2(10), eqpa_tsp_port_1 VARCHAR2(5), eqpa_tsp_port_2 VARCHAR2(5), eqpa_tsp_port_3 VARCHAR2(5), eqpa_lease_number VARCHAR2(25), eqpa_lease_type VARCHAR2(10), eqpa_out_width_left NUMBER, eqpa_out_width_right NUMBER, eqpa_out_length_fore NUMBER, eqpa_out_length_back NUMBER, eqpa_ccm_gen_status VARCHAR2(1) DEFAULT 'N' NOT NULL, eqpa_ccm_file_name VARCHAR2(50), eqpa_ccm_on DATE, eqpa_tsp_status VARCHAR2(1) DEFAULT 'N', eqpa_batch VARCHAR2(25), eqpa_filter_0 VARCHAR2(240), eqpa_filter_1 VARCHAR2(240), eqpa_filter_2 VARCHAR2(240), eqpa_filter_3 VARCHAR2(240), eqpa_filter_4 VARCHAR2(240), eqpa_filter_5 VARCHAR2(240), eqpa_filter_6 VARCHAR2(240), eqpa_filter_7 VARCHAR2(240), eqpa_filter_8 VARCHAR2(240), eqpa_filter_9 VARCHAR2(240), eqpa_commission_status VARCHAR2(1) DEFAULT 'N', eqpa_slot_operator VARCHAR2(10), eqpa_terminal VARCHAR2(10), eqpa_haulage VARCHAR2(1), eqpa_from_sub_location VARCHAR2(10), eqpa_to_sub_location VARCHAR2(10), eqpa_barge_voyage VARCHAR2(10), eqpa_barge_name VARCHAR2(100), eqpa_current_loc_depot VARCHAR2(10), eqpa_quality_status VARCHAR2(1), eqpa_dest_location VARCHAR2(10), eqpa_dest_depot VARCHAR2(10), eqpa_demage_reason VARCHAR2(2000), eqpa_demage_remarks VARCHAR2(2000), eqpa_repair_reference VARCHAR2(240), eqpa_invoice_to VARCHAR2(10), eqpa_tsp_port_4 VARCHAR2(10), eqpa_tsp_port_5 VARCHAR2(10), eqpa_reefer_temp_basis VARCHAR2(1), eqpa_orgin_depot VARCHAR2(10), eqpa_orgin_location VARCHAR2(10), eqpa_origin_loc_name VARCHAR2(100), eqpa_orgin_zip VARCHAR2(50), eqpa_dest_loc_name VARCHAR2(100), eqpa_dest_loc_zip VARCHAR2(50), eqpa_feeder_voyage VARCHAR2(10), eqpa_feeder_vessel VARCHAR2(10), eqpa_dest_terminal VARCHAR2(10), eqpa_scac_code VARCHAR2(10), eqpa_pre_booking_number VARCHAR2(25), eqpa_lod_dis_lot_number NUMBER, eqpa_line_reference VARCHAR2(240), eqpa_other_reference VARCHAR2(240), eqpa_contract_no VARCHAR2(240), eqpa_dest_type VARCHAR2(1), eqpa_to_loc VARCHAR2(5), eqpa_to_loc_name VARCHAR2(75), eqpa_program_id VARCHAR2(50), eqpa_seal_type_1 VARCHAR2(1) DEFAULT 'N', eqpa_seal_type_2 VARCHAR2(1) DEFAULT 'N', eqpa_seal_type_3 VARCHAR2(1) DEFAULT 'N', eqpa_seal_number_2 VARCHAR2(25), eqpa_seal_number_3 VARCHAR2(25), eqpa_flex_6 VARCHAR2(240), eqpa_flex_7 VARCHAR2(240), eqpa_flex_8 VARCHAR2(240), eqpa_flex_9 VARCHAR2(240), eqpa_flex_10 VARCHAR2(240), eqpa_iso_code VARCHAR2(10), eqpa_pay_load NUMBER, eqpa_local_gen_status VARCHAR2(1), eqpa_local_file_on DATE, eqpa_local_file_name VARCHAR2(50), eqpa_cont_dep_doc_type VARCHAR2(10), eqpa_cont_dep_doc_number VARCHAR2(50), eqpa_seal_type VARCHAR2(1), eqpa_open_bal_remarks VARCHAR2(2000), eqpa_tsp_comm VARCHAR2(1) ) -- Indexes for ID_EQUIPMENT_ALL_MOVES

-----------------   INDEX ------------------

CREATE INDEX cont_edi_idx ON id_equipment_all_moves (eqpa_company, eqpa_line, eqpa_ccm_gen_status) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_bill_idx ON id_equipment_all_moves (eqpa_company, eqpa_bill_no, eqpa_move_code) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_cont_status ON id_equipment_all_moves (eqpa_company, eqpa_container, eqpa_current_status) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_created_date_key ON id_equipment_all_moves (eqpa_company, eqpa_created_on) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_current_cont_move_dt ON id_equipment_all_moves (eqpa_company, eqpa_current_status, eqpa_container, eqpa_move_date, eqpa_move_code, eqpa_current_location) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_current_status ON id_equipment_all_moves (eqpa_company, eqpa_current_status) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_line_key ON id_equipment_all_moves (eqpa_company, eqpa_line, eqpa_vessel, eqpa_voyage, eqpa_port) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_move_date_idx ON id_equipment_all_moves (eqpa_company, eqpa_move_date) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX equipment_all_moves_ind ON id_equipment_all_moves (eqpa_company, eqpa_customer) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE UNIQUE INDEX equipment_unique_key ON id_equipment_all_moves (eqpa_company, eqpa_container, eqpa_move_date, eqpa_move_code) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX euipment_all_moves_ind ON id_equipment_all_moves (eqpa_container_type) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX euipment_all_moves_ind2 ON id_equipment_all_moves (eqpa_container_service_type) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX euipment_all_moves_ind3 ON id_equipment_all_moves (eqpa_line) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX euipment_all_moves_ind4 ON id_equipment_all_moves (eqpa_vessel) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX euipment_all_moves_ind5 ON id_equipment_all_moves (eqpa_move_code) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX fsa_eqc_dem_idx ON id_equipment_all_moves (eqpa_company, eqpa_line, eqpa_current_location, eqpa_move_code, eqpa_move_date, eqpa_bl_number) PCTFREE 10 INITRANS 2 MAXTRANS 255 / 
-----------------------------------------------------------------------


CREATE OR REPLACE VIEW iv_equipment_all_moves_gv ( eqpa_company, eqpa_container, eqpa_serial_no, eqpa_move_date, eqpa_move_code, eqpa_service_type, eqpa_container_service_type, eqpa_container_type, eqpa_container_size, eqpa_container_storage, eqpa_mode, eqpa_current_location, eqpa_current_status, eqpa_gross_weight, eqpa_tare_weight, eqpa_net_weight, eqpa_cargo_weight, eqpa_days, eqpa_created_by, eqpa_created_on, eqpa_modified_by, eqpa_modified_on, eqpa_line, eqpa_vessel, eqpa_voyage, eqpa_port, eqpa_mother_line, eqpa_mother_vessel, eqpa_mother_voyage, eqpa_mother_port, eqpa_origin_port, eqpa_origin_name, eqpa_from_port, eqpa_to_port, eqpa_final_port, eqpa_final_name, eqpa_truck_number, eqpa_transporter, eqpa_customer, eqpa_seal_number, eqpa_bill_no, eqpa_bl_number, eqpa_do_number, eqpa_remarks, eqpa_out_gauge, eqpa_out_height, eqpa_out_width, eqpa_out_length, eqpa_reefer_status, eqpa_min_temprature, eqpa_max_temprature, eqpa_imco_status, eqpa_imco_unno, eqpa_imco_class, eqpa_imco_page, eqpa_imco_flash, eqpa_food_status, eqpa_food_description, eqpa_booking_type, eqpa_booking_number, eqpa_job_type, eqpa_job_number, eqpa_job_basis, eqpa_amount, eqpa_train_number, eqpa_train_reference, eqpa_account_status, eqpa_booking_status, eqpa_billing_status, eqpa_flex_1, eqpa_flex_2, eqpa_flex_3, eqpa_flex_4, eqpa_flex_5, eqpa_entry_type, eqpa_stowage, eqpa_commodity, eqpa_sequence, eqpa_soc, eqpa_voyage_service, eqpa_tsp_port_1, eqpa_tsp_port_2, eqpa_tsp_port_3, eqpa_lease_number, eqpa_lease_type, eqpa_out_width_left, eqpa_out_width_right, eqpa_out_length_fore, eqpa_out_length_back, eqpa_ccm_gen_status, eqpa_ccm_file_name, eqpa_ccm_on, eqpa_tsp_status, eqpa_batch, eqpa_filter_0, eqpa_filter_1, eqpa_filter_2, eqpa_filter_3, eqpa_filter_4, eqpa_filter_5, eqpa_filter_6, eqpa_filter_7, eqpa_filter_8, eqpa_filter_9, eqpa_commission_status, eqpa_slot_operator, eqpa_terminal, eqpa_haulage, eqpa_from_sub_location, eqpa_to_sub_location, eqpa_barge_voyage, eqpa_barge_name, eqpa_current_loc_depot, eqpa_quality_status, eqpa_dest_location, eqpa_dest_depot, eqpa_demage_reason, eqpa_demage_remarks, eqpa_repair_reference, eqpa_invoice_to, eqpa_tsp_port_4, eqpa_tsp_port_5, eqpa_reefer_temp_basis, eqpa_orgin_depot, eqpa_orgin_location, eqpa_origin_loc_name, eqpa_orgin_zip, eqpa_dest_loc_name, eqpa_dest_loc_zip, eqpa_feeder_voyage, eqpa_feeder_vessel, eqpa_dest_terminal, eqpa_scac_code, eqpa_pre_booking_number, eqpa_lod_dis_lot_number, eqpa_line_reference, eqpa_other_reference, eqpa_contract_no, eqpa_dest_type, eqpa_to_loc, eqpa_to_loc_name, eqpa_program_id, eqpa_seal_type_1, eqpa_seal_type_2, eqpa_seal_type_3, eqpa_seal_number_2, eqpa_seal_number_3, eqpa_flex_6, eqpa_flex_7, eqpa_flex_8, eqpa_flex_9, eqpa_flex_10, eqpa_local_gen_status, eqpa_local_file_name, eqpa_local_file_on, eqpa_iso_code, eqpa_pay_load, eqpa_cont_dep_doc_type, eqpa_cont_dep_doc_number) AS
SELECT "EQPA_COMPANY",
       "EQPA_CONTAINER",
       "EQPA_SERIAL_NO",
       "EQPA_MOVE_DATE",
       "EQPA_MOVE_CODE",
       "EQPA_SERVICE_TYPE",
       "EQPA_CONTAINER_SERVICE_TYPE",
       "EQPA_CONTAINER_TYPE",
       "EQPA_CONTAINER_SIZE",
       "EQPA_CONTAINER_STORAGE",
       "EQPA_MODE",
       "EQPA_CURRENT_LOCATION",
       "EQPA_CURRENT_STATUS",
       "EQPA_GROSS_WEIGHT",
       "EQPA_TARE_WEIGHT",
       "EQPA_NET_WEIGHT",
       "EQPA_CARGO_WEIGHT",
       "EQPA_DAYS",
       "EQPA_CREATED_BY",
       "EQPA_CREATED_ON",
       "EQPA_MODIFIED_BY",
       "EQPA_MODIFIED_ON",
       "EQPA_LINE",
       "EQPA_VESSEL",
       "EQPA_VOYAGE",
       "EQPA_PORT",
       "EQPA_MOTHER_LINE",
       "EQPA_MOTHER_VESSEL",
       "EQPA_MOTHER_VOYAGE",
       "EQPA_MOTHER_PORT",
       "EQPA_ORIGIN_PORT",
       "EQPA_ORIGIN_NAME",
       "EQPA_FROM_PORT",
       "EQPA_TO_PORT",
       "EQPA_FINAL_PORT",
       "EQPA_FINAL_NAME",
       "EQPA_TRUCK_NUMBER",
       "EQPA_TRANSPORTER",
       "EQPA_CUSTOMER",
       "EQPA_SEAL_NUMBER",
       "EQPA_BILL_NO",
       "EQPA_BL_NUMBER",
       "EQPA_DO_NUMBER",
       "EQPA_REMARKS",
       "EQPA_OUT_GAUGE",
       "EQPA_OUT_HEIGHT",
       "EQPA_OUT_WIDTH",
       "EQPA_OUT_LENGTH",
       "EQPA_REEFER_STATUS",
       "EQPA_MIN_TEMPRATURE",
       "EQPA_MAX_TEMPRATURE",
       "EQPA_IMCO_STATUS",
       "EQPA_IMCO_UNNO",
       "EQPA_IMCO_CLASS",
       "EQPA_IMCO_PAGE",
       "EQPA_IMCO_FLASH",
       "EQPA_FOOD_STATUS",
       "EQPA_FOOD_DESCRIPTION",
       "EQPA_BOOKING_TYPE",
       "EQPA_BOOKING_NUMBER",
       "EQPA_JOB_TYPE",
       "EQPA_JOB_NUMBER",
       "EQPA_JOB_BASIS",
       "EQPA_AMOUNT",
       "EQPA_TRAIN_NUMBER",
       "EQPA_TRAIN_REFERENCE",
       "EQPA_ACCOUNT_STATUS",
       "EQPA_BOOKING_STATUS",
       "EQPA_BILLING_STATUS",
       "EQPA_FLEX_1",
       "EQPA_FLEX_2",
       "EQPA_FLEX_3",
       "EQPA_FLEX_4",
       "EQPA_FLEX_5",
       "EQPA_ENTRY_TYPE",
       "EQPA_STOWAGE",
       "EQPA_COMMODITY",
       "EQPA_SEQUENCE",
       "EQPA_SOC",
       "EQPA_VOYAGE_SERVICE",
       "EQPA_TSP_PORT_1",
       "EQPA_TSP_PORT_2",
       "EQPA_TSP_PORT_3",
       "EQPA_LEASE_NUMBER",
       "EQPA_LEASE_TYPE",
       "EQPA_OUT_WIDTH_LEFT",
       "EQPA_OUT_WIDTH_RIGHT",
       "EQPA_OUT_LENGTH_FORE",
       "EQPA_OUT_LENGTH_BACK",
       "EQPA_CCM_GEN_STATUS",
       "EQPA_CCM_FILE_NAME",
       "EQPA_CCM_ON",
       "EQPA_TSP_STATUS",
       "EQPA_BATCH",
       "EQPA_FILTER_0",
       "EQPA_FILTER_1",
       "EQPA_FILTER_2",
       "EQPA_FILTER_3",
       "EQPA_FILTER_4",
       "EQPA_FILTER_5",
       "EQPA_FILTER_6",
       "EQPA_FILTER_7",
       "EQPA_FILTER_8",
       "EQPA_FILTER_9",
       "EQPA_COMMISSION_STATUS",
       "EQPA_SLOT_OPERATOR",
       "EQPA_TERMINAL",
       "EQPA_HAULAGE",
       "EQPA_FROM_SUB_LOCATION",
       "EQPA_TO_SUB_LOCATION",
       "EQPA_BARGE_VOYAGE",
       "EQPA_BARGE_NAME",
       "EQPA_CURRENT_LOC_DEPOT",
       "EQPA_QUALITY_STATUS",
       "EQPA_DEST_LOCATION",
       "EQPA_DEST_DEPOT",
       "EQPA_DEMAGE_REASON",
       "EQPA_DEMAGE_REMARKS",
       "EQPA_REPAIR_REFERENCE",
       "EQPA_INVOICE_TO",
       "EQPA_TSP_PORT_4",
       "EQPA_TSP_PORT_5",
       "EQPA_REEFER_TEMP_BASIS",
       "EQPA_ORGIN_DEPOT",
       "EQPA_ORGIN_LOCATION",
       "EQPA_ORIGIN_LOC_NAME",
       "EQPA_ORGIN_ZIP",
       "EQPA_DEST_LOC_NAME",
       "EQPA_DEST_LOC_ZIP",
       "EQPA_FEEDER_VOYAGE",
       "EQPA_FEEDER_VESSEL",
       "EQPA_DEST_TERMINAL",
       "EQPA_SCAC_CODE",
       "EQPA_PRE_BOOKING_NUMBER",
       "EQPA_LOD_DIS_LOT_NUMBER",
       "EQPA_LINE_REFERENCE",
       "EQPA_OTHER_REFERENCE",
       "EQPA_CONTRACT_NO",
       "EQPA_DEST_TYPE",
       "EQPA_TO_LOC",
       "EQPA_TO_LOC_NAME",
       "EQPA_PROGRAM_ID",
       "EQPA_SEAL_TYPE_1",
       "EQPA_SEAL_TYPE_2",
       "EQPA_SEAL_TYPE_3",
       "EQPA_SEAL_NUMBER_2",
       "EQPA_SEAL_NUMBER_3",
       "EQPA_FLEX_6",
       "EQPA_FLEX_7",
       "EQPA_FLEX_8",
       "EQPA_FLEX_9",
       "EQPA_FLEX_10",
       "EQPA_LOCAL_GEN_STATUS",
       "EQPA_LOCAL_FILE_NAME",
       "EQPA_LOCAL_FILE_ON",
       "EQPA_ISO_CODE",
       "EQPA_PAY_LOAD",
       "EQPA_CONT_DEP_DOC_TYPE",
       "EQPA_CONT_DEP_DOC_NUMBER"
FROM id_equipment_all_moves


CREATE TABLE id_equipment_control ( eqip_company VARCHAR2(10) NOT NULL, eqip_full_in VARCHAR2(10) NOT NULL, eqip_full_tsp_in VARCHAR2(10) NOT NULL, eqip_empty_in VARCHAR2(10) NOT NULL, eqip_empty_tsp_in VARCHAR2(10) NOT NULL, eqip_full_out VARCHAR2(10) NOT NULL, eqip_full_tsp_out VARCHAR2(10) NOT NULL, eqip_empty_out VARCHAR2(10) NOT NULL, eqip_empty_tsp_out VARCHAR2(10) NOT NULL, eqip_depot_in VARCHAR2(10) NOT NULL, eqip_depot_out VARCHAR2(10) NOT NULL, eqip_lease_in VARCHAR2(10), eqip_lease_out VARCHAR2(10), eqip_damage_in VARCHAR2(10), eqip_damage_out VARCHAR2(10), eqip_return_empty VARCHAR2(10) NOT NULL, eqip_return_full VARCHAR2(10), eqip_expense_build VARCHAR2(1) DEFAULT 'N' NOT NULL, eqip_sold VARCHAR2(10), eqip_redelivery VARCHAR2(10), eqip_depot_empty_in VARCHAR2(10), eqip_depot_empty_out VARCHAR2(10), eqip_van VARCHAR2(10), eqip_dvan VARCHAR2(10), eqip_sublease_in VARCHAR2(10), eqip_sublease_out VARCHAR2(10), eqip_icd_arrival VARCHAR2(10), eqip_to_repair VARCHAR2(10), eqip_from_repair VARCHAR2(10), eqip_purchase VARCHAR2(10), eqip_filter_0 VARCHAR2(240), eqip_filter_1 VARCHAR2(240), eqip_filter_2 VARCHAR2(240), eqip_filter_3 VARCHAR2(240), eqip_filter_4 VARCHAR2(240), eqip_filter_5 VARCHAR2(240), eqip_filter_6 VARCHAR2(240), eqip_filter_7 VARCHAR2(240), eqip_filter_8 VARCHAR2(240), eqip_filter_9 VARCHAR2(240), eqip_delv VARCHAR2(10), eqip_full_o_truck VARCHAR2(10), eqip_full_i_truck VARCHAR2(10), eqip_full_o_rail VARCHAR2(10), eqip_full_i_rail VARCHAR2(10), eqip_mty_o_truck VARCHAR2(10), eqip_mty_i_truck VARCHAR2(10), eqip_mty_o_rail VARCHAR2(10), eqip_mty_i_rail VARCHAR2(10), eqip_xstuff VARCHAR2(10), eqip_mty_gate_out_soc VARCHAR2(10), eqip_dock_destuff VARCHAR2(10), eqip_dock_stuff VARCHAR2(10), eqip_fact_destuff VARCHAR2(10), eqip_fact_stuff VARCHAR2(10), eqip_rep_ret_mty VARCHAR2(10), eqip_sub_out_full VARCHAR2(10), eqip_sub_out_mty VARCHAR2(10), eqip_sub_in_full VARCHAR2(10), eqip_sub_in_mty VARCHAR2(10), eqip_repair_out_full VARCHAR2(10), eqip_repair_out_mty VARCHAR2(10), eqip_repair_ret_full VARCHAR2(10), eqip_damage_full VARCHAR2(10), eqip_damage_mty VARCHAR2(10), eqip_repair_ret_mty VARCHAR2(10), eqip_exp_dtfi VARCHAR2(10), eqip_exp_dtfo VARCHAR2(10), eqip_imp_dtfi VARCHAR2(10), eqip_imp_dtfo VARCHAR2(10), eqip_reserved_full VARCHAR2(10), eqip_rew_out VARCHAR2(10), eqip_redirect_delivery VARCHAR2(10), eqip_redirect_pickup VARCHAR2(10), eqip_reserved_mty VARCHAR2(10), eqip_sold_full VARCHAR2(10), eqip_exp_out_truck VARCHAR2(10), eqip_exp_in_truck VARCHAR2(10), eqip_exp_out_rail VARCHAR2(10), eqip_exp_in_rail VARCHAR2(10), eqip_imp_out_truck VARCHAR2(10), eqip_imp_in_truck VARCHAR2(10), eqip_imp_out_rail VARCHAR2(10), eqip_imp_in_rail VARCHAR2(10), eqip_rew_in VARCHAR2(10), eqip_ldfbg VARCHAR2(10), eqip_ldmbg VARCHAR2(10), eqip_dsfbg VARCHAR2(10), eqip_dsmbg VARCHAR2(10), eqip_ucacgo VARCHAR2(10), eqip_lost_full VARCHAR2(10), eqip_lost_mty VARCHAR2(10), eqip_from_cfs_mty VARCHAR2(10), eqip_to_cfs_mty VARCHAR2(10), eqip_from_cfs_full VARCHAR2(10), eqip_to_cfs_full VARCHAR2(10) )

trace
TKPROF: Release 10.2.0.5.0 - Production on Mon Sep 24 23:19:28 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: msctest_ora_9264_MYSQLTRACE.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

error connecting to database using: msctest_ora_9264_MYSQLTRACE.trc
ORA-01017: invalid username/password; logon denied

EXPLAIN PLAN option disabled.
********************************************************************************

select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
  i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
  i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
  nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
  i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
  nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
  null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
  ist.logicalread 
from
 ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
  min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) 
  valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where 
  i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          4           0
Execute     16      0.01       0.13          0          0          0           0
Fetch       81      0.00       0.00          0        208          0          65
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      101      0.01       0.14          0        208          4          65

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
     17  SORT ORDER BY (cr=29 pr=0 pw=0 time=781 us)
     17   HASH JOIN OUTER (cr=29 pr=0 pw=0 time=834 us)
     17    NESTED LOOPS OUTER (cr=25 pr=0 pw=0 time=323 us)
     17     TABLE ACCESS CLUSTER IND$ (cr=23 pr=0 pw=0 time=129 us)
      1      INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=16 us)(object id 3)
      0     TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=2 pr=0 pw=0 time=134 us)
      0      INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=2 pr=0 pw=0 time=65 us)(object id 712)
      2    VIEW  (cr=4 pr=0 pw=0 time=72 us)
      2     SORT GROUP BY (cr=4 pr=0 pw=0 time=66 us)
      2      TABLE ACCESS CLUSTER CDEF$ (cr=4 pr=0 pw=0 time=25 us)
      1       INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=9 us)(object id 30)

********************************************************************************

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, 
  sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, 
  spare1, spare2, avgcln 
from
 hist_head$ where obj#=:1 and intcol#=:2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          8           0
Execute    196      0.00       0.48          0          0          0           0
Fetch      196      0.00       0.00          0        776          0         188
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      396      0.00       0.48          0        776          8         188

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=4 pr=0 pw=0 time=38 us)
      1   INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=3 pr=0 pw=0 time=23 us)(object id 257)

********************************************************************************

select /*+ rule */ bucket, endpoint, col#, epvalue 
from
 histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          2           0
Execute    223      0.00       0.00          0          0          0           0
Fetch      222      0.00       0.01          0        666          0        3766
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      447      0.01       0.01          0        666          2        3766

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
     20  SORT ORDER BY (cr=3 pr=0 pw=0 time=96 us)
     20   TABLE ACCESS CLUSTER HISTGRM$ (cr=3 pr=0 pw=0 time=58 us)
      1    INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=10 us)(object id 252)

********************************************************************************

select pos#,intcol#,col#,spare1,bo#,spare2 
from
 icol$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          4           0
Execute     65      0.00       0.00          0          0          0           0
Fetch      177      0.00       0.00          0        354          0         112
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      246      0.00       0.00          0        354          4         112

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  TABLE ACCESS BY INDEX ROWID ICOL$ (cr=12 pr=0 pw=0 time=23 us)
      5   INDEX RANGE SCAN I_ICOL1 (cr=7 pr=0 pw=0 time=33 us)(object id 40)

********************************************************************************

select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
  nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
  scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
  rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
  nvl(spare3,0) 
from
 col$ where obj#=:1 order by intcol#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          2           0
Execute     18      0.00       0.00          0          0          0           0
Fetch     1046      0.00       0.00          0        157          0        1028
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1068      0.01       0.01          0        157          2        1028

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
    162  SORT ORDER BY (cr=6 pr=0 pw=0 time=586 us)
    162   TABLE ACCESS CLUSTER COL$ (cr=6 pr=0 pw=0 time=340 us)
      1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=9 us)(object id 3)

********************************************************************************

select text 
from
 view$ where rowid=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          4          0           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=9 us)

********************************************************************************

select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) 
from
 objauth$ where obj#=:1 and col# is not null group by privilege#, col#, 
  grantee# order by col#, grantee#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          8           0
Execute      7      0.00       0.00          0          0          0           0
Fetch        7      0.00       0.00          0         23          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       18      0.00       0.00          0         23          8           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT GROUP BY (cr=3 pr=0 pw=0 time=42 us)
      0   TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=3 pr=0 pw=0 time=30 us)
      0    INDEX RANGE SCAN I_OBJAUTH1 (cr=3 pr=0 pw=0 time=24 us)(object id 103)

********************************************************************************

select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
 objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by 
  grantee#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          8           0
Execute      7      0.00       0.00          0          0          0           0
Fetch      118      0.00       0.00          0         40          0         111
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      129      0.00       0.00          0         40          8         111

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
     23  SORT GROUP BY (cr=6 pr=0 pw=0 time=145 us)
     23   TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=6 pr=0 pw=0 time=183 us)
     23    INDEX RANGE SCAN I_OBJAUTH1 (cr=3 pr=0 pw=0 time=151 us)(object id 103)

********************************************************************************

select con#,obj#,rcon#,enabled,nvl(defer,0) 
from
 cdef$ where robj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          8           0
Execute     11      0.00       0.00          0          0          0           0
Fetch      129      0.00       0.00          0        260          0         118
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      144      0.00       0.00          0        260          8         118

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  TABLE ACCESS BY INDEX ROWID CDEF$ (cr=6 pr=0 pw=0 time=21 us)
      2   INDEX RANGE SCAN I_CDEF3 (cr=4 pr=0 pw=0 time=15 us)(object id 52)

********************************************************************************

select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
  rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) 
from
 cdef$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          8           0
Execute     11      0.00       0.00          0          0          0           0
Fetch      222      0.00       0.00          0        247          0         211
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      237      0.00       0.00          0        247          8         211

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
     44  TABLE ACCESS CLUSTER CDEF$ (cr=48 pr=0 pw=0 time=20 us)
      1   INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=10 us)(object id 30)

********************************************************************************

select intcol#,nvl(pos#,0),col#,nvl(spare1,0) 
from
 ccol$ where con#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0         10           0
Execute    211      0.00       0.00          0          0          0           0
Fetch      442      0.00       0.00          0        884          0         231
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      656      0.00       0.01          0        884         10         231

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID CCOL$ (cr=4 pr=0 pw=0 time=20 us)
      1   INDEX RANGE SCAN I_CCOL1 (cr=3 pr=0 pw=0 time=17 us)(object id 54)

********************************************************************************

select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, 
  spare2 
from
 obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null 
  and linkname is null and subname is null


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0         24          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       15      0.00       0.00          0         24          0           6

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID OBJ$ (cr=4 pr=0 pw=0 time=24 us)
      1   INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=14 us)(object id 37)

********************************************************************************

select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#, 
  nvl(typidcol#, 0) 
from
 coltype$ where obj#=:1 order by intcol# desc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          8           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0         13          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0         13          8           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=5 pr=0 pw=0 time=44 us)
      0   TABLE ACCESS CLUSTER COLTYPE$ (cr=5 pr=0 pw=0 time=36 us)
      1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=10 us)(object id 3)

********************************************************************************

select intcol#, toid, version#, intcols, intcol#s, flags, synobj# 
from
 subcoltype$ where obj#=:1 order by intcol# asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          8           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0         13          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0         13          8           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=5 pr=0 pw=0 time=29 us)
      0   TABLE ACCESS CLUSTER SUBCOLTYPE$ (cr=5 pr=0 pw=0 time=19 us)
      1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=10 us)(object id 3)

********************************************************************************

select col#,intcol#,ntab# 
from
 ntab$ where obj#=:1 order by intcol# asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          8           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0          3          8           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS BY INDEX ROWID NTAB$ (cr=1 pr=0 pw=0 time=16 us)
      0   INDEX RANGE SCAN I_NTAB2 (cr=1 pr=0 pw=0 time=11 us)(object id 200)

********************************************************************************

select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk, 
  l.pctversion$, l.flags, l.property, l.retention, l.freepools 
from
 lob$ l where l.obj# = :1 order by l.intcol# asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          6           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0         13          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.00       0.00          0         13          6           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=5 pr=0 pw=0 time=28 us)
      0   TABLE ACCESS CLUSTER LOB$ (cr=5 pr=0 pw=0 time=20 us)
      1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=10 us)(object id 3)

********************************************************************************

select col#,intcol#,reftyp,stabid,expctoid 
from
 refcon$ where obj#=:1 order by intcol# asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          8           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0          3          8           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS BY INDEX ROWID REFCON$ (cr=1 pr=0 pw=0 time=14 us)
      0   INDEX RANGE SCAN I_REFCON2 (cr=1 pr=0 pw=0 time=10 us)(object id 204)

********************************************************************************

select col#,intcol#,charsetid,charsetform 
from
 col$ where obj#=:1 order by intcol# asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          8           0
Execute      3      0.00       0.00          0          0          0           0
Fetch      363      0.00       0.00          0         13          0         360
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      369      0.00       0.00          0         13          8         360

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
    162  SORT ORDER BY (cr=5 pr=0 pw=0 time=446 us)
    162   TABLE ACCESS CLUSTER COL$ (cr=5 pr=0 pw=0 time=337 us)
      1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=8 us)(object id 3)

********************************************************************************

select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,  elemnum 
from
 opqtype$ where obj# = :1 order by intcol# asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          8           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0          3          8           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS BY INDEX ROWID OPQTYPE$ (cr=1 pr=0 pw=0 time=16 us)
      0   INDEX RANGE SCAN I_OPQTYPE1 (cr=1 pr=0 pw=0 time=10 us)(object id 206)

********************************************************************************

select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
  o.dataobj#,o.flags 
from
 obj$ o where o.obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0         18          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.00       0.00          0         18          0           6

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=19 us)
      1   INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=10 us)(object id 36)

********************************************************************************

select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
  NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),
  NVL(scanhint,0) 
from
 seg$ where ts#=:1 and file#=:2 and block#=:3


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          4           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          9          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0          9          4           3

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS CLUSTER SEG$ (cr=3 pr=0 pw=0 time=37 us)
      1   INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=26 us)(object id 9)

********************************************************************************

select condition 
from
 cdef$ where rowid=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0         10           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          4         10           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY USER ROWID CDEF$ (cr=1 pr=0 pw=0 time=10 us)

********************************************************************************

explain plan for(SELECT 'Full Import on Terminal'                         move_code,
       eqpa_container_type,
       Count(eqpa_container)                             cont,
       SUM(Nvl(Decode(eqpa_container_size, '20', 1,
                                           '40', 2), 0)) teus
FROM   mtu_2009.id_equipment_all_moves
WHERE  eqpa_company = 'MTU'
       AND eqpa_move_date BETWEEN '01-JAN-2009' AND '23-SEP-2012'
       AND eqpa_move_code = 'DSFULL'
       AND ( eqpa_company, eqpa_container, eqpa_serial_no ) IN (SELECT
a.eqpa_company,
                                                        a.eqpa_container,
                                                        Max(a.eqpa_serial_no)
                                                 FROM
mtu_2009.iv_equipment_all_moves_gv a,
mtu_2009.id_equipment_control
                                                 WHERE
a.eqpa_company = 'MTU'
AND a.eqpa_company = eqip_company
AND a.eqpa_container = eqpa_container
AND a.eqpa_container_type = eqpa_container_type
AND a.eqpa_move_date <= SYSDATE
AND eqpa_soc = 'L'
                                                 GROUP  BY a.eqpa_company,
                                                           a.eqpa_container)
GROUP  BY eqpa_container_type)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.05       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.05       0.05          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us)
      0   HASH JOIN RIGHT SEMI (cr=0 pr=0 pw=0 time=0 us)
      0    VIEW  VW_NSO_1 (cr=0 pr=0 pw=0 time=0 us)
      0     HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us)
      0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
      0       INDEX UNIQUE SCAN EQUIPMENT_CONTROL_KEY (cr=0 pr=0 pw=0 time=0 us)(object id 52984)
      0       TABLE ACCESS FULL ID_EQUIPMENT_ALL_MOVES (cr=0 pr=0 pw=0 time=0 us)
      0    TABLE ACCESS FULL ID_EQUIPMENT_ALL_MOVES (cr=0 pr=0 pw=0 time=0 us)

********************************************************************************

select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,
  nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,
  t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,
  t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),
  nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),
  nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,
  ts.logicalread 
from
 tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          2           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          8          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          8          2           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  MERGE JOIN OUTER (cr=4 pr=0 pw=0 time=51 us)
      1   TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=19 us)
      1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=8 us)(object id 3)
      0   BUFFER SORT (cr=1 pr=0 pw=0 time=18 us)
      0    TABLE ACCESS BY INDEX ROWID TAB_STATS$ (cr=1 pr=0 pw=0 time=11 us)
      0     INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=1 pr=0 pw=0 time=5 us)(object id 710)

********************************************************************************

insert into plan_table (statement_id, timestamp, operation, options,
  object_node, object_owner, object_name, object_instance, object_type,
  search_columns, id, parent_id, position, other,optimizer, cost, cardinality,
   bytes, other_tag, partition_start, partition_stop, partition_id, 
  distribution, cpu_cost, io_cost, temp_space, access_predicates, 
  filter_predicates, projection, time, qblock_name, object_alias, plan_id, 
  depth, remarks, other_xml ) 
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,
  :22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      9      0.00       0.00          0          7         15           9
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.00       0.00          0          7         15           9

Misses in library cache during parse: 1
Misses in library cache during execute: 5
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************

SELECT ORA_PLAN_ID_SEQ$.NEXTVAL 
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          0          1           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          1           1

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SEQUENCE  ORA_PLAN_ID_SEQ$ (cr=53 pr=0 pw=0 time=4627 us)
      1   FAST DUAL  (cr=0 pr=0 pw=0 time=5 us)

********************************************************************************

select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,
  flags 
from
 seq$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0          4          0           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  TABLE ACCESS BY INDEX ROWID SEQ$ (cr=4 pr=0 pw=0 time=30 us)
      2   INDEX UNIQUE SCAN I_SEQ1 (cr=2 pr=0 pw=0 time=15 us)(object id 102)

********************************************************************************

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
  cache=:7,highwater=:8,audit$=:9,flags=:10 
where
 obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          2           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          2           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  SEQ$ (cr=1 pr=0 pw=0 time=138 us)
      1   INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=45 us)(object id 102)

********************************************************************************

ALTER SESSION SET sql_trace = false


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           0

Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: SYS
********************************************************************************

ALTER SESSION SET sql_trace = true


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
********************************************************************************

ALTER SESSION SET tracefile_identifier = mysqltrace


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Parsing user id: SYS
********************************************************************************

SELECT 'Full Import on Terminal'                         move_code,
       eqpa_container_type,
       Count(eqpa_container)                             cont,
       SUM(Nvl(Decode(eqpa_container_size, '20', 1,
                                           '40', 2), 0)) teus
FROM   mtu_2009.id_equipment_all_moves
WHERE  eqpa_company = 'MTU'
       AND eqpa_move_date BETWEEN '01-JAN-2009' AND '23-SEP-2012'
       AND eqpa_move_code = 'DSFULL'
       AND ( eqpa_company, eqpa_container, eqpa_serial_no ) IN (SELECT
a.eqpa_company,
                                                        a.eqpa_container,
                                                        Max(a.eqpa_serial_no)
                                                 FROM
mtu_2009.iv_equipment_all_moves_gv a,
mtu_2009.id_equipment_control
                                                 WHERE
a.eqpa_company = 'MTU'
AND a.eqpa_company = eqip_company
AND a.eqpa_container = eqpa_container
AND a.eqpa_container_type = eqpa_container_type
AND a.eqpa_move_date <= SYSDATE
AND eqpa_soc = 'L'
                                                 GROUP  BY a.eqpa_company,
                                                           a.eqpa_container)
GROUP  BY eqpa_container_type

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      6.45      78.30     222550     234699          0          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      6.48      78.33     222550     234699          0          11

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     11  HASH GROUP BY (cr=234699 pr=222550 pw=13062 time=78300501 us)
  10989   HASH JOIN RIGHT SEMI (cr=234699 pr=222550 pw=13062 time=78282826 us)
 489572    VIEW  VW_NSO_1 (cr=117350 pr=122633 pw=10073 time=71520730 us)
 489572     HASH GROUP BY (cr=117350 pr=122633 pw=10073 time=68583298 us)
2372718      NESTED LOOPS  (cr=117350 pr=112560 pw=0 time=43016920 us)
      1       INDEX UNIQUE SCAN EQUIPMENT_CONTROL_KEY (cr=1 pr=0 pw=0 time=15 us)(object id 52984)
2372718       TABLE ACCESS FULL ID_EQUIPMENT_ALL_MOVES (cr=117349 pr=112560 pw=0 time=38271465 us)
 274978    TABLE ACCESS FULL ID_EQUIPMENT_ALL_MOVES (cr=117349 pr=93526 pw=0 time=1375128 us)




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.08       0.09          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        2      6.45      78.30     222550     234699          0          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      6.54      78.39     222550     234699          0          11

Misses in library cache during parse: 4
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       78      0.02       0.02          0          0        132           0
Execute    820      0.08       0.67          0          8         17          10
Fetch     3046      0.04       0.04          0       3747          1        6215
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3944      0.15       0.74          0       3755        150        6225

Misses in library cache during parse: 27
Misses in library cache during execute: 30

    6  user  SQL statements in session.
  811  internal SQL statements in session.
  817  SQL statements in session.
    0  statements EXPLAINed in this session.
********************************************************************************
Trace file: msctest_ora_9264_MYSQLTRACE.trc
Trace file compatibility: 10.01.00
Sort options: default

       0  session in tracefile.
       6  user  SQL statements in trace file.
     811  internal SQL statements in trace file.
     817  SQL statements in trace file.
      32  unique SQL statements in trace file.
    8704  lines in trace file.
     210  elapsed seconds in trace file.



-------------------- scripts----------------------------------------------------
CREATE TABLE id_equipment_all_moves ( eqpa_company VARCHAR2(10) NOT NULL, eqpa_container VARCHAR2(15) NOT NULL, eqpa_serial_no NUMBER NOT NULL, eqpa_move_date DATE NOT NULL, eqpa_move_code VARCHAR2(10) NOT NULL, eqpa_service_type VARCHAR2(10) NOT NULL, eqpa_container_service_type VARCHAR2(10), eqpa_container_type VARCHAR2(10) NOT NULL, eqpa_container_size VARCHAR2(2) NOT NULL, eqpa_container_storage VARCHAR2(10) NOT NULL, eqpa_mode VARCHAR2(1) DEFAULT 'S' NOT NULL, eqpa_current_location VARCHAR2(5) NOT NULL, eqpa_current_status VARCHAR2(1) DEFAULT 'C' NOT NULL, eqpa_gross_weight NUMBER DEFAULT 0 NOT NULL, eqpa_tare_weight NUMBER DEFAULT 0 NOT NULL, eqpa_net_weight NUMBER DEFAULT 0 NOT NULL, eqpa_cargo_weight NUMBER DEFAULT 0 NOT NULL, eqpa_days NUMBER DEFAULT 0 NOT NULL, eqpa_created_by VARCHAR2(10) NOT NULL, eqpa_created_on DATE DEFAULT sysdate NOT NULL, eqpa_modified_by VARCHAR2(10), eqpa_modified_on DATE, eqpa_line VARCHAR2(10), eqpa_vessel VARCHAR2(10), eqpa_voyage VARCHAR2(10), eqpa_port VARCHAR2(5), eqpa_mother_line VARCHAR2(10), eqpa_mother_vessel VARCHAR2(10), eqpa_mother_voyage VARCHAR2(10), eqpa_mother_port VARCHAR2(5), eqpa_origin_port VARCHAR2(5), eqpa_origin_name VARCHAR2(50), eqpa_from_port VARCHAR2(5), eqpa_to_port VARCHAR2(5), eqpa_final_port VARCHAR2(5), eqpa_final_name VARCHAR2(50), eqpa_truck_number VARCHAR2(25), eqpa_transporter VARCHAR2(50), eqpa_customer VARCHAR2(10), eqpa_seal_number VARCHAR2(25), eqpa_bill_no VARCHAR2(50), eqpa_bl_number VARCHAR2(25), eqpa_do_number VARCHAR2(25), eqpa_remarks VARCHAR2(240), eqpa_out_gauge VARCHAR2(1) DEFAULT 'N', eqpa_out_height NUMBER, eqpa_out_width NUMBER, eqpa_out_length NUMBER, eqpa_reefer_status VARCHAR2(1) DEFAULT 'N', eqpa_min_temprature NUMBER, eqpa_max_temprature NUMBER, eqpa_imco_status VARCHAR2(1) DEFAULT 'N', eqpa_imco_unno VARCHAR2(10), eqpa_imco_class VARCHAR2(10), eqpa_imco_page VARCHAR2(10), eqpa_imco_flash VARCHAR2(10), eqpa_food_status VARCHAR2(1) DEFAULT 'N', eqpa_food_description VARCHAR2(240), eqpa_booking_type VARCHAR2(10), eqpa_booking_number VARCHAR2(25), eqpa_job_type VARCHAR2(10), eqpa_job_number VARCHAR2(25), eqpa_job_basis VARCHAR2(50), eqpa_amount NUMBER DEFAULT 0, eqpa_train_number VARCHAR2(25), eqpa_train_reference VARCHAR2(50), eqpa_account_status VARCHAR2(1) DEFAULT 'X', eqpa_booking_status VARCHAR2(1) DEFAULT 'X', eqpa_billing_status VARCHAR2(1) DEFAULT 'X', eqpa_flex_1 VARCHAR2(240), eqpa_flex_2 VARCHAR2(240), eqpa_flex_3 VARCHAR2(240), eqpa_flex_4 VARCHAR2(240), eqpa_flex_5 VARCHAR2(240) DEFAULT 'N', eqpa_entry_type VARCHAR2(1) DEFAULT 'D' NOT NULL, eqpa_stowage VARCHAR2(10), eqpa_commodity VARCHAR2(10), eqpa_sequence NUMBER, eqpa_soc VARCHAR2(1) DEFAULT 'L', eqpa_voyage_service VARCHAR2(10), eqpa_tsp_port_1 VARCHAR2(5), eqpa_tsp_port_2 VARCHAR2(5), eqpa_tsp_port_3 VARCHAR2(5), eqpa_lease_number VARCHAR2(25), eqpa_lease_type VARCHAR2(10), eqpa_out_width_left NUMBER, eqpa_out_width_right NUMBER, eqpa_out_length_fore NUMBER, eqpa_out_length_back NUMBER, eqpa_ccm_gen_status VARCHAR2(1) DEFAULT 'N' NOT NULL, eqpa_ccm_file_name VARCHAR2(50), eqpa_ccm_on DATE, eqpa_tsp_status VARCHAR2(1) DEFAULT 'N', eqpa_batch VARCHAR2(25), eqpa_filter_0 VARCHAR2(240), eqpa_filter_1 VARCHAR2(240), eqpa_filter_2 VARCHAR2(240), eqpa_filter_3 VARCHAR2(240), eqpa_filter_4 VARCHAR2(240), eqpa_filter_5 VARCHAR2(240), eqpa_filter_6 VARCHAR2(240), eqpa_filter_7 VARCHAR2(240), eqpa_filter_8 VARCHAR2(240), eqpa_filter_9 VARCHAR2(240), eqpa_commission_status VARCHAR2(1) DEFAULT 'N', eqpa_slot_operator VARCHAR2(10), eqpa_terminal VARCHAR2(10), eqpa_haulage VARCHAR2(1), eqpa_from_sub_location VARCHAR2(10), eqpa_to_sub_location VARCHAR2(10), eqpa_barge_voyage VARCHAR2(10), eqpa_barge_name VARCHAR2(100), eqpa_current_loc_depot VARCHAR2(10), eqpa_quality_status VARCHAR2(1), eqpa_dest_location VARCHAR2(10), eqpa_dest_depot VARCHAR2(10), eqpa_demage_reason VARCHAR2(2000), eqpa_demage_remarks VARCHAR2(2000), eqpa_repair_reference VARCHAR2(240), eqpa_invoice_to VARCHAR2(10), eqpa_tsp_port_4 VARCHAR2(10), eqpa_tsp_port_5 VARCHAR2(10), eqpa_reefer_temp_basis VARCHAR2(1), eqpa_orgin_depot VARCHAR2(10), eqpa_orgin_location VARCHAR2(10), eqpa_origin_loc_name VARCHAR2(100), eqpa_orgin_zip VARCHAR2(50), eqpa_dest_loc_name VARCHAR2(100), eqpa_dest_loc_zip VARCHAR2(50), eqpa_feeder_voyage VARCHAR2(10), eqpa_feeder_vessel VARCHAR2(10), eqpa_dest_terminal VARCHAR2(10), eqpa_scac_code VARCHAR2(10), eqpa_pre_booking_number VARCHAR2(25), eqpa_lod_dis_lot_number NUMBER, eqpa_line_reference VARCHAR2(240), eqpa_other_reference VARCHAR2(240), eqpa_contract_no VARCHAR2(240), eqpa_dest_type VARCHAR2(1), eqpa_to_loc VARCHAR2(5), eqpa_to_loc_name VARCHAR2(75), eqpa_program_id VARCHAR2(50), eqpa_seal_type_1 VARCHAR2(1) DEFAULT 'N', eqpa_seal_type_2 VARCHAR2(1) DEFAULT 'N', eqpa_seal_type_3 VARCHAR2(1) DEFAULT 'N', eqpa_seal_number_2 VARCHAR2(25), eqpa_seal_number_3 VARCHAR2(25), eqpa_flex_6 VARCHAR2(240), eqpa_flex_7 VARCHAR2(240), eqpa_flex_8 VARCHAR2(240), eqpa_flex_9 VARCHAR2(240), eqpa_flex_10 VARCHAR2(240), eqpa_iso_code VARCHAR2(10), eqpa_pay_load NUMBER, eqpa_local_gen_status VARCHAR2(1), eqpa_local_file_on DATE, eqpa_local_file_name VARCHAR2(50), eqpa_cont_dep_doc_type VARCHAR2(10), eqpa_cont_dep_doc_number VARCHAR2(50), eqpa_seal_type VARCHAR2(1), eqpa_open_bal_remarks VARCHAR2(2000), eqpa_tsp_comm VARCHAR2(1) ) -- Indexes for ID_EQUIPMENT_ALL_MOVES

CREATE INDEX cont_edi_idx ON id_equipment_all_moves (eqpa_company, eqpa_line, eqpa_ccm_gen_status) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_bill_idx ON id_equipment_all_moves (eqpa_company, eqpa_bill_no, eqpa_move_code) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_cont_status ON id_equipment_all_moves (eqpa_company, eqpa_container, eqpa_current_status) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_created_date_key ON id_equipment_all_moves (eqpa_company, eqpa_created_on) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_current_cont_move_dt ON id_equipment_all_moves (eqpa_company, eqpa_current_status, eqpa_container, eqpa_move_date, eqpa_move_code, eqpa_current_location) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_current_status ON id_equipment_all_moves (eqpa_company, eqpa_current_status) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_line_key ON id_equipment_all_moves (eqpa_company, eqpa_line, eqpa_vessel, eqpa_voyage, eqpa_port) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX eqpa_move_date_idx ON id_equipment_all_moves (eqpa_company, eqpa_move_date) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX equipment_all_moves_ind ON id_equipment_all_moves (eqpa_company, eqpa_customer) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE UNIQUE INDEX equipment_unique_key ON id_equipment_all_moves (eqpa_company, eqpa_container, eqpa_move_date, eqpa_move_code) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX euipment_all_moves_ind ON id_equipment_all_moves (eqpa_container_type) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX euipment_all_moves_ind2 ON id_equipment_all_moves (eqpa_container_service_type) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX euipment_all_moves_ind3 ON id_equipment_all_moves (eqpa_line) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX euipment_all_moves_ind4 ON id_equipment_all_moves (eqpa_vessel) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX euipment_all_moves_ind5 ON id_equipment_all_moves (eqpa_move_code) PCTFREE 10 INITRANS 2 MAXTRANS 255 /
CREATE INDEX fsa_eqc_dem_idx ON id_equipment_all_moves (eqpa_company, eqpa_line, eqpa_current_location, eqpa_move_code, eqpa_move_date, eqpa_bl_number) PCTFREE 10 INITRANS 2 MAXTRANS 255 / -- Constraints for ID_EQUIPMENT_ALL_MOVES

ALTER TABLE id_equipment_all_moves ADD CONSTRAINT all_move_key PRIMARY KEY (eqpa_company,
                                                                            eqpa_container,
                                                                            eqpa_serial_no) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 /
ALTER TABLE id_equipment_all_moves ADD CONSTRAINT eqpa_seq_unique UNIQUE (eqpa_company,
                                                                          eqpa_sequence) /
ALTER TABLE id_equipment_all_moves ADD CONSTRAINT equipment_unique_key UNIQUE (eqpa_company,
                                                                               eqpa_container,
                                                                               eqpa_move_date,
                                                                               eqpa_move_code) /
ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_carrier_check
FOREIGN KEY (eqpa_line) REFERENCES ID_LINE_MASTER(line_code) /
ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_code_check
FOREIGN KEY (eqpa_move_code) REFERENCES ID_MOVE_MASTER(move_code) /
ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_container_check
FOREIGN KEY (eqpa_company,
             eqpa_container) REFERENCES ID_EQUIPMENT_MASTER(eqp_company,eqp_container) /
ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_container_service_check
FOREIGN KEY (eqpa_container_service_type) REFERENCES ID_SERVICE_MASTER(service_code) /
ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_conttype_check
FOREIGN KEY (eqpa_container_type) REFERENCES ID_CONTTYPE_MASTER(container_type) /
ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_customer_check
FOREIGN KEY (eqpa_company,
             eqpa_customer) REFERENCES ID_SHIP_CUSTOMER_MASTER(scm_company,scm_code) /
ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_vessel_check
FOREIGN KEY (eqpa_vessel) REFERENCES ID_VESSEL_MASTER(vessel_code) /
ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_voyage_check
FOREIGN KEY (eqpa_company,
             eqpa_line,
             eqpa_vessel,
             eqpa_voyage,
             eqpa_port) REFERENCES ID_VOYAGE_MASTER(voyage_company,voyage_line,voyage_vessel,voyage_voyage, voyage_port) /
ALTER TABLE id_equipment_all_moves ADD CONSTRAINT account_status_check CHECK (eqpa_account_status IN ('X' ,
                                                                                                      'N' ,
                                                                                                      'P' ,
                                                                                                      'T')) /
ALTER TABLE id_equipment_all_moves ADD CONSTRAINT eqpa_mode_check CHECK ( eqpa_mode IN ('S' ,
                                                                                        'R' ,
                                                                                        'T' ,
                                                                                        'N',
                                                                                        'B')) /
ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_soc IN ('L' ,
                                                           'S')) /
ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_entry_type IN ('R' ,
                                                                  'T' ,
                                                                  'D' ,
                                                                  'A' ,
                                                                  'I' ,
                                                                  'E')) /
ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_food_status IN ('Y' ,
                                                                   'N')) /
ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_booking_status IN ('P' ,
                                                                      'N' ,
                                                                      'X')) /
ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_billing_status IN ('X' ,
                                                                      'N' ,
                                                                      'P')) /
ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_service_type IN ('LCL' ,
                                                                    'MTY' ,
                                                                    'FTSP' ,
                                                                    'MTSP' ,
                                                                    'FCL')) /
ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_current_status IN ('C' ,
                                                                      'D' ,
                                                                      'H')) /
ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_out_gauge IN ('Y' ,
                                                                 'N')) /
ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_reefer_status IN ('Y' ,
                                                                     'N')) /
ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_imco_status IN ('Y' ,
                                                                   'N')) / -- End of DDL script for ID_EQUIPMENT_ALL_MOVES


Previous Topic: can I incorporate two counts in a single query ?
Next Topic: SERIALLY_REUSABLE packages
Goto Forum:
  


Current Time: Fri Oct 31 02:51:10 CDT 2014

Total time taken to generate the page: 0.19329 seconds