Skip to content

Added cardinality to SQL on xmltable to avoid excessive estimates. #756

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
jgebal merged 4 commits into from
Sep 26, 2018
Merged

Added cardinality to SQL on xmltable to avoid excessive estimates. #756

Changes from 2 commits
Commits
Show all changes
4 commits
Select commit Hold shift + click to select a range
a966451
Added cardinality to SQL on xmltable to avoid excessive estimates.
jgebal Sep 24, 2018
eb0dab7
Additional cardinality hint.
jgebal Sep 25, 2018
a8bac08
Merge branch 'develop' into feature/ut_cursor_compare_cardinality
jgebal Sep 25, 2018
0f592e6
Cleanup of cardinality hints.
jgebal Sep 25, 2018
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
60 changes: 33 additions & 27 deletions source/expectations/data_values/ut_compound_data_helper.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -91,41 +91,46 @@ create or replace package body ut_compound_data_helper is
l_results tt_column_diffs;
begin
l_column_filter := get_columns_row_filter(a_exclude_xpath, a_include_xpath);
--CARDINALITY hints added to address issue: https://github.com/utPLSQL/utPLSQL/issues/752
l_sql := q'[
with
expected_cols as ( select :a_expected as item_data from dual ),
actual_cols as ( select :a_actual as item_data from dual ),
expected_cols_info as (
select e.*,
expected_cols_info as (
select /*+ cardinality(e 100) */
e.*,
replace(expected_type,'VARCHAR2','CHAR') expected_type_compare
from (
SELECT rownum expected_pos,
xt.name expected_name,
xt.type expected_type
FROM (select ]'||l_column_filter||q'[ from expected_cols ucd) x,
XMLTABLE('/ROW/*'
PASSING x.item_data
COLUMNS
name VARCHAR2(4000) PATH '@xml_valid_name',
type VARCHAR2(4000) PATH '/'
) xt
) e
select /*+ cardinality(xt 100) */
rownum expected_pos,
xt.name expected_name,
xt.type expected_type
from (select ]'||l_column_filter||q'[ from expected_cols ucd) x,
xmltable(
'/ROW/*'
passing x.item_data
columns
name varchar2(4000) PATH '@xml_valid_name',
type varchar2(4000) PATH '/'
) xt
) e
),
actual_cols_info as (
select a.*,
select /*+ cardinality(a 100) */
a.*,
replace(actual_type,'VARCHAR2','CHAR') actual_type_compare
from (
SELECT rownum actual_pos,
xt.name actual_name,
xt.type actual_type
FROM (select ]'||l_column_filter||q'[ from actual_cols ucd) x,
XMLTABLE('/ROW/*'
PASSING x.item_data
COLUMNS
name VARCHAR2(4000) PATH '@xml_valid_name',
type VARCHAR2(4000) PATH '/'
) xt
) a
from (select /*+ cardinality(xt 100) */
rownum actual_pos,
xt.name actual_name,
xt.type actual_type
from (select ]'||l_column_filter||q'[ from actual_cols ucd) x,
xmltable('/ROW/*'
passing x.item_data
columns
name varchar2(4000) path '@xml_valid_name',
type varchar2(4000) path '/'
) xt
) a
),
joined_cols as (
select e.*, a.*,
Expand All @@ -134,7 +139,8 @@ create or replace package body ut_compound_data_helper is
from expected_cols_info e
full outer join actual_cols_info a on e.expected_name = a.actual_name
)
select case
select /*+ cardinality(joined_cols 100)*/
case
when expected_pos is null and actual_pos is not null then '+'
when expected_pos is not null and actual_pos is null then '-'
when expected_type_compare != actual_type_compare then 't'
Expand Down