BIT OR

Currently, Oracle has only one bitwise operator - BITAND. All other bitwise operators, such as
BITOR, BITXOR, BITNAND, BITNOR, BITXNOR and BITNOT, must be derived from BITAND
and/or other (from it) consecutively derived operators.

I've created three functions for three bitwise operators : BITOR, BITXOR and BITNOT:


-- BITOR
CREATE FUNCTION "SYS"."BITOR" (x IN NUMBER, y IN NUMBER) RETURN NUMBER AS
BEGIN
RETURN (x + y - BITAND(x, y));
END;
/



GRANT EXECUTE ON SYS.BITOR TO PUBLIC;

CREATE PUBLIC SYNONYM "BITOR" FOR "SYS"."BITOR";

-- BITXOR
CREATE FUNCTION "SYS"."BITXOR" (x IN NUMBER, y IN NUMBER) RETURN NUMBER AS
BEGIN
RETURN (BITOR(x, y) - BITAND(x, y));
END;
/
GRANT EXECUTE ON SYS.BITXOR TO PUBLIC;
CREATE PUBLIC SYNONYM "BITXOR" FOR "SYS"."BITXOR";

-- BITNOT
CREATE FUNCTION "SYS"."BITNOT" (x IN NUMBER) RETURN NUMBER AS
BEGIN
RETURN (-1 - x);
END;
/
GRANT EXECUTE ON SYS.BITNOT TO PUBLIC;
CREATE PUBLIC SYNONYM "BITNOT" FOR "SYS"."BITNOT";

The functions are created in SYS schema and works well. My question is:

whenever a system-wide accessible function (a function available to all users just like
any other Oracle's built-in function) is to be created, what is appropriate place to
create it? Is it SYS, SYSTEM or SYSAUX schema?

Also, if anyone has a better coding solution for these functions (operators), please let
me know.

Thanks a lot.

Albert

Oracle - usuwanie wierszy

1)
DELETE FROM student WHERE
(student.course, student.major) IN
(SELECT schedule.course, schedule.major FROM schedule)


2)
delete s
from
student s
inner join schedule sch
on s.course=sch.course
and s.major = sch.major

3)
DELETE FROM student WHERE EXISTS ( SELECT 1 FROM schedule WHERE schedule.course=student.course AND schedule.major=student.major )

Oracle - Hints

/*+ opt_param('optimizer_index_cost_adj',1) */

Oracle - usuwanie duplikatów

http://www.dba-oracle.com/t_delete_duplicate_table_rows.htm


Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques. This page shows some examples of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:

DELETE FROM
table_name A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
table_name B
WHERE
A.col1 = B.col1
AND
A.col2 = B.col2
);


This outstanding article show an example of the RANK function to identify and remove duplicate rows from Oracle tables:
"It can be frustrating to find a way to delete all of the duplicate rows without deleting the initial instances.

delete from $table_name where rowid in
(
select "rowid" from
(select "rowid", rank_n from
(select rank() over (partition by $primary_key order by rowid) rank_n, rowid as "rowid"
from $table_name
where $primary_key in
(select $primary_key from $table_name
group by $all_columns
having count(*) > 1
)
)
)
where rank_n > 1
)

This query selects all of the ‘extra’ rowids and removes them. It is especially designed for limiting the query scans to only those records which have duplicates, which is useful if there’s only a subset of the table that you are dealing with. If you want to improve its efficiency for a table with a high percentage of duplicates, simply remove the inside where clause."

Optimizer Hints

http://download.oracle.com/docs/cd/A97630_01/server.920/a96533/hintsref.htm#5790


Oracle SQL Hints Tuning - http://www.dba-oracle.com/t_sql_hints_tuning.htm