I have three tables with below structure:
create table customer_info
(customer_num number,
customer_firstName varchar2(100),
customer_lastName varchar2(100),
branch_code varchar2(100));
create table branch_info
(branch_code varchar2(100),
branch_name varchar2(100));
create table customer_transaction
(transaction_date date,
customer_num number,
branch_code varchar2(100),
transaction_count number
)
Among these tables the one which has the biggest amount of data is customer_transaction with nearly 10 million records right now. The table has partition on column transaction_date .I want to know:
1)whether there is a difference between these two queries in terms of performance.
2)In what way these two queries are different from each other.
The point is that in the first query I'm using this /*+parallel(4)*/ and in the second query I'm referencing the table which has the biggest amount of data /*+parallel(t2,4)*/.I must say that both queries produce different execution plans.
First Query:
Select /*+parallel(4)*/
t2.transaction_date,
t1.customer_num,
t1.customer_firstname,
t1.customer_lastname,
t3.branch_name,
t2.transaction_count
from customer_info t1
left join customer_transaction t2
on t1.customer_num = t2.customer_num
left join branch_info t3
on t2.branch_code = t3.branch_code;
Second Query:
Select /*+parallel(t2,4)*/ -----> t2 is "customer_transaction"
t2.transaction_date,
t1.customer_num,
t1.customer_firstname,
t1.customer_lastname,
t3.branch_name,
t2.transaction_count
from customer_info t1
left join customer_transaction t2
on t1.customer_num = t2.customer_num
left join branch_info t3
on t2.branch_code = t3.branch_code;
Thanks in advance.
explain planon both variants and see how they differ.