cte1, besides needing a better name, needs to just be a fully-fledged table. With 63 combinations, it takes a while to process how to get all these in correctly. Putting this in a view doesn't speed up that CTE at all. So, just create a permanent table in the database preloaded with these values.
That should cut a massive chunk out of your query time.
I'll compliment in saying that formatting, capitalization, etc. are consistent.
cte1 and cte2 are not very good names. Aliases should reflect what they actually represent.
To me the gorilla in the room is that UNION is used 64 times to explicitly spell out every possible scenario instead of using logic. Let me illustrate.
PS: Note I'm ...
I'm afraid you current solution is a prime example of inappropriate use of PL/SQL when plain SQL provides much more elegant solution:
create table t1 (
create table t2 (
create table t3 (
insert into t1 values(1,'A');
insert into t2 values(2);
insert into t3 values(3);
insert into t1 ...
I am not sure there is a way to reduce the number of if-statements and nesting. What is possible though is to improve the performance of the conditions you have that check those conditions.
The count(*) is inefficient. Consider adding a 'ROWNUM = 1' condition to your where clause in a way that makes the following conditions possible:
I am using this trigger to keep a copy of the deleted record from table Orders_Details in table Orders_Details_Deleted.
No, you're not. The code copies all rows from orders_details to order_details_deleted every time the trigger is run.
Is there any better way [than using a trigger] ?
Yes, there is a better way. Don't reinvent database features but use ...
Your core problem is how to find out top-1 and bottom-1 in a single query. This is essentially a SQL problem and has not much to do with PL/SQL. First I present you the SQL solution and then simply wrap that into PL/SQL.
You didn't provided table definitions. I was lazy and didn't tried to reverse engineer your schema but instead created very simple table ...
I would move PackageName and Name elements of the AbstractProcedureType to attributes.
I don't think you need an AbstractProcedureType, just have a ProcedureType and FunctionType derived from it.
it's not clear why ArgumentsType may have either 0 or 1 argument... I would say that it should have maxOccurs="unbounded"
I would make Arguments element optional in ...
This is very much the same idea I'm using to encapsulate "business logic" queries for re-use in PL/SQL code. So if you're looking for a validation of a concept you'll pass my review :) In a comment to the other answer you said this is just a snippet from a package code - that's good you're using packages. For PL/SQL apps in practice all code should be ...
The query plan seems to be too complicated because all_constraint is a view and not table. So it's difficult to derive what it's doing.
But from common sense it seems the optimal execution plan would be this:
It first selects all ('P', 'U') records in the inner query
For each of them finds correspondent 'R' records (with loop join)
Filters and sorts what ...
Check this function which returns the mid point X,Y in WKT format. Please note this function uses the SDO_LRS of Oracle which is part of Oracle Spatial.
create or replace function get_line_midpoint
(line_in IN sde.st_geometry)
-- RETURN sde.st_geometry
I would recommend to try re-writing the procedure with Buffer and Centroid functions as below.
procedure mindpoint (in_line_geometry)
line_buffer_geom = sde.st_buffer (in_line_geometry, 0.05)
cenrtroid_buffer_geom = sde.st_centroid (line_buffer_geom )
This whole thing is nothing you should do with a stored procedure. What you have here is a simple (sub)select:
(SELECT (SELECT COUNT(*) FROM grade WHERE [...]) > 1) AS exists
btw. I think you meant to write >= 1 which would work better when you only have one existing record :D
As already visible from above codesnippet I have some slightly different ...
I'd solve the problem by providing a PL/SQL procedure that knows the "split"-logic.
In the example below I assume that non-splitted rows are not inserted into the table at all but all inserts should be done with the procedure. This is a rather common pattern when there's data manipulations but it is not clear from your problem statement if this is an ...