Tham khảo tài liệu 'oracle sql internals handbook phần 2', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | Now with iteration abilities we have all the ingredients for writing the parser. Like traditional software practice we start by writing a unit test first WITH src as Select a 1 or b 1 and y 3 or z 1 and c 1 and x 5 or z 3 and y 7 exprfrom dual . We refactored the src subquery into a separate view because it would be used in multiple places. Oracle isn t automatically refactoring the clauses that aren t explicitly declared so. Next we find all delimiter positions idxs as select i from select rownum i from TABLE UNSAFE where rownum 4000 a src where i LENGTH EXPR and substr EXPR i 1 or substr EXPR i 1 or substr EXPR i 1 The rownum 4000 predicate effectively limits parsing strings to 4000 characters only. In an ideal world this predicate wouldn t be there. The subquery would produce rows indefinitely until some outer condition signaled that the task is completed so that producer could stop then. Among those delimiters we are specifically interested in positions of all left brackets lbri as select i from idxs src where substr EXPR i 1 The right bracket positions view - rbri and whitespaces wtsp are defined similarly. All these three views can be defined directly without introduction of idxs view of course. However it is much more efficient to push in predicates early and deal with 4 Oracle SQL Internals Handbook idxs view which has much lower cardinality than select rownum i from TABLE UNSAFE where rownum 4000. Now that we have indexed and classified all the delimiter positions we ll build a list of all the clauses which begins and ends at the delimiter positions and then filter out the irrelevant clauses. We extract the segment s start and end points first begi as select i 1 x from wtsp union all select i x from lbri union all select i 1 x from Ibri endi as x y select i y from wtsp union all select i 1 y from rbri union all select i y from rbri Note that in case of brackets we consider multiple combinations of clauses - with and without brackets. Unlike starting point
